package com.bci.pwtz.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.channels.FileChannel;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.zip.ZipOutputStream;

import com.bci.pwtz.mysql.dao.*;
import com.bci.pwtz.mysql.model.*;
import com.bci.pwtz.service.DyxlService;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import com.bci.pwtz.common.PwtzConstants;
import com.bci.pwtz.common.util.PwtzUtils;
import com.bci.pwtz.exception.PwtzDatabaseException;
import com.bci.pwtz.service.ExcelUtilService;

public class ExcelUtilServiceImpl implements ExcelUtilService {

    private ExportTemplateMapper exportTemplateMapper;

    private ExportTemplateDefMapper exportTemplateDefMapper;

    private ConfigMapper configMapper;

    @Autowired
    private DyxlService dyxlService;

    private ImportTemplateDefMapper importTemplateDefMapper;

    private ImportTemplateMapper importTemplateMapper;

    private JdbcTemplate jdbcTemplate;

    private SimpleJdbcTemplate simpleJdbcTemplate;

    public DyxlService getDyxlService() {
        return dyxlService;
    }

    public void setDyxlService(DyxlService dyxlService) {
        this.dyxlService = dyxlService;
    }

    /**
     * @return the jdbcTemplate
     */
    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    /**
     * @param simpleJdbcTemplate the simpleJdbcTemplate to set
     */
    public void setSimpleJdbcTemplate(SimpleJdbcTemplate simpleJdbcTemplate) {
        this.simpleJdbcTemplate = simpleJdbcTemplate;
    }

    /**
     * @return the simpleJdbcTemplate
     */
    public SimpleJdbcTemplate getSimpleJdbcTemplate() {
        return simpleJdbcTemplate;
    }

    /**
     * @param jdbcTemplate the jdbcTemplate to set
     */
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * @return the exportTemplateMapper
     */
    public ExportTemplateMapper getExportTemplateMapper() {
        return exportTemplateMapper;
    }

    /**
     * @param exportTemplateMapper the exportTemplateMapper to set
     */
    public void setExportTemplateMapper(
            ExportTemplateMapper exportTemplateMapper) {
        this.exportTemplateMapper = exportTemplateMapper;
    }

    /**
     * @return the exportTemplateDefMapper
     */
    public ExportTemplateDefMapper getExportTemplateDefMapper() {
        return exportTemplateDefMapper;
    }

    /**
     * @param exportTemplateDefMapper the exportTemplateDefMapper to set
     */
    public void setExportTemplateDefMapper(
            ExportTemplateDefMapper exportTemplateDefMapper) {
        this.exportTemplateDefMapper = exportTemplateDefMapper;
    }

    /**
     * @return the configMapper
     */
    public ConfigMapper getConfigMapper() {
        return configMapper;
    }

    /**
     * @param configMapper the configMapper to set
     */
    public void setConfigMapper(ConfigMapper configMapper) {
        this.configMapper = configMapper;
    }

    /**
     * @return the importTemplateDefMapper
     */
    public ImportTemplateDefMapper getImportTemplateDefMapper() {
        return importTemplateDefMapper;
    }

    public void setImportTemplateMapper(
            ImportTemplateMapper importTemplateMapper) {
        this.importTemplateMapper = importTemplateMapper;
    }

    public ImportTemplateMapper getImportTemplateMapper() {
        return importTemplateMapper;
    }

    /**
     * @param importTemplateDefMapper the importTemplateDefMapper to set
     */
    public void setImportTemplateDefMapper(
            ImportTemplateDefMapper importTemplateDefMapper) {
        this.importTemplateDefMapper = importTemplateDefMapper;
    }

    private static final Logger _logger = Logger
            .getLogger(ExcelUtilServiceImpl.class);

    @Override
    public String exportExcle(long companyId, long templateId,
                              String stratTime, String endTime) throws PwtzDatabaseException {
        // 返回成功失败表示:2为失败
        String returnCode = "2";
        try {
            // 通过companyId获取Excel的工作书册 Workbook,对应到一个excel文档
            ExportTemplate exportTemplate = exportTemplateMapper
                    .selectTemplateByIdAndStatus(templateId, 1);
            endTime += "235959";
            if (exportTemplate != null) {
                _logger.info("exportTemplate.getExcelPath()="
                        + exportTemplate.getExcelPath());
                String excelPath = exportTemplate.getExcelPath();
                File excelFile = new File(excelPath);
                if (!excelFile.exists()) {
                    return returnCode;
                }
                HSSFWorkbook wb = new HSSFWorkbook();
                try {
                    wb = new HSSFWorkbook(new FileInputStream(excelFile));
                } catch (FileNotFoundException e) {
                    return returnCode;
                } catch (IOException e) {
                    return returnCode;
                }
                // 通过companyId获取Excel的工作sheet,对应到多个excel文档的tab
                List<ExportTemplateDef> exportTemplateDefList = exportTemplateDefMapper
                        .selectTemplateDefByTemplateId(templateId);
                _logger.info("exportTemplateDefList size ="
                        + exportTemplateDefList.size());
                if (exportTemplateDefList.size() > 0) {
                    for (int i = 0; i < exportTemplateDefList.size(); i++) {
                        ExportTemplateDef exportTemplateDef = new ExportTemplateDef();
                        exportTemplateDef = exportTemplateDefList.get(i);
                        // 通过EXECUTE_SQL生成对应每个sheet提取的表数据
                        String sheetSql = exportTemplateDef.getExecuteSql();
                        Map mapSqlParm = new HashMap();

                        mapSqlParm.put("CompanyId", companyId);
                        mapSqlParm.put("StartTime", stratTime);
                        mapSqlParm.put("EndTime", endTime);
                        // List<Map<String, Object>> mapList =
                        // jdbcTemplate.queryForList(sheetSql, mapSqlParm);
                        List<Map<String, Object>> mapList = simpleJdbcTemplate
                                .queryForList(sheetSql, mapSqlParm);
                        _logger.info("mapList size =" + mapList.size());
                        // 获取对应sheet
                        _logger.info("exportTemplateDef.getSheetName() ="
                                + exportTemplateDef.getSheetName());
                        HSSFSheet sheet = wb.getSheet(exportTemplateDef
                                .getSheetName());
                        // 获取赋值行数
                        int rowIndex = exportTemplateDef.getStartRowIndex();
                        // 通过mapList进行excle赋值
                        if (mapList.size() > 0) {
                            for (int j = 0; j < mapList.size(); j++) {
                                HSSFRow row = sheet.createRow(rowIndex);
                                Map map = mapList.get(j);
                                HSSFCell cell = row.createCell(0);
                                sheet.autoSizeColumn(0, true);
                                cell.setCellValue(j + 1);
                                int k = 1;
                                for (Iterator it = map.keySet().iterator(); it
                                        .hasNext(); ) {
                                    String key = (String) it.next();
                                    cell = row.createCell(k);
                                    sheet.autoSizeColumn(k, true);
                                    cell.setCellValue((map.get(key) == null || map.get(key).equals("null") || map.get(key).equals("NULL")) ? "" : map.get(key) + "");
                                    k++;
                                }
                                rowIndex++;
                            }
                        } else {
                            _logger.info("jdbcTemplate.queryForList(" + sheetSql
                                    + "):mapList.size() =0");
                            if ((i + 1) == mapList.size()) {
                                return returnCode;
                            }
                        }
                    }
                    // 读取输出文件路径并输出excle
                    String dir = "";
                    String fileName = "";
                    List<Config> exportDataPathList = configMapper
                            .selectByKey("ExportDataPath");
                    if (exportDataPathList != null && exportDataPathList.size() > 0) {
                        dir = exportDataPathList.get(0).getConfigValue();
                        dir = dir.replace("@COMPANYID", String.valueOf(companyId));
                        dir = dir.replace("@TIMESTAMP", String.valueOf(System
                                .currentTimeMillis()));
                    }
                    // 临时存放图片文件夹
                    //String tempstr = "D:\\" + System.currentTimeMillis();
                    File fdir = new File(dir);
                    if (!fdir.exists()) {
                        fdir.mkdirs();
                    }
                    List<Config> exportExcelNameList = configMapper
                            .selectByKey("ExportExcelName");
                    if (exportExcelNameList != null
                            && exportExcelNameList.size() > 0) {
                        fileName = exportExcelNameList.get(0).getConfigValue();
                    }
                    _logger.info("dir=" + dir);
                    _logger.info("fileName=" + fileName);
                    FileOutputStream os = null;
                    try {
                        //os = new FileOutputStream(dir + "\\test1.xls");
                        os = new FileOutputStream(dir + fileName);
                        wb.write(os);
                        os.close();
                        returnCode = dir;
                    } catch (FileNotFoundException e) {
                        e.printStackTrace();
                    } catch (IOException e) {
                        e.printStackTrace();
                    } finally {
                        try {
                            os.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                } else {
                    _logger
                            .info("exportTemplateDefMapper.selectTemplateDefByTemplateId("
                                    + templateId
                                    + "):exportTemplateDefList.size() =0");
                }
            } else {
                _logger.info("exportTemplateMapper.selectTemplateByIdAndStatus("
                        + templateId + ",1):is null");
            }

            _logger.info("returnCode =======" + returnCode);
        } catch (Exception e) {
            _logger.info(e);
            e.printStackTrace();
        }
        return returnCode;
    }

    @Override
    public String exportYXExcle(long companyId, long templateId) throws PwtzDatabaseException {
        String returnCode = "2";
        try {
            // 通过companyId获取Excel的工作书册 Workbook,对应到一个excel文档
            ExportTemplate exportTemplate = exportTemplateMapper
                    .selectTemplateByIdAndStatus(templateId, 1);
            if (exportTemplate != null) {
                _logger.info("exportTemplate.getExcelPath()="
                        + exportTemplate.getExcelPath());
                String excelPath = exportTemplate.getExcelPath();
                File excelFile = new File(excelPath);
                if (!excelFile.exists()) {
                    return returnCode;
                }
                HSSFWorkbook wb = new HSSFWorkbook();
                try {
                    wb = new HSSFWorkbook(new FileInputStream(excelFile));
                } catch (FileNotFoundException e) {
                    return returnCode;
                } catch (IOException e) {
                    return returnCode;
                }
                // 通过companyId获取Excel的工作sheet,对应到多个excel文档的tab
                List<ExportTemplateDef> exportTemplateDefList = exportTemplateDefMapper
                        .selectTemplateDefByTemplateId(templateId);
                _logger.info("exportTemplateDefList size ="
                        + exportTemplateDefList.size());
                if (exportTemplateDefList.size() > 0) {
                    for (int i = 0; i < exportTemplateDefList.size(); i++) {
                        ExportTemplateDef exportTemplateDef = new ExportTemplateDef();
                        exportTemplateDef = exportTemplateDefList.get(i);
                        // 通过EXECUTE_SQL生成对应每个sheet提取的表数据
                        String sheetSql = exportTemplateDef.getExecuteSql();
                        Map mapSqlParm = new HashMap();

//						mapSqlParm.put("CompanyId", companyId);
                        // List<Map<String, Object>> mapList =
                        // jdbcTemplate.queryForList(sheetSql, mapSqlParm);
                        List<Map<String, Object>> mapList = simpleJdbcTemplate
                                .queryForList(sheetSql, companyId);
                        _logger.info("mapList size =" + mapList.size());
                        // 获取对应sheet
                        _logger.info("exportTemplateDef.getSheetName() ="
                                + exportTemplateDef.getSheetName());
                        HSSFSheet sheet = wb.getSheet(exportTemplateDef
                                .getSheetName());
                        // 获取赋值行数
                        int rowIndex = exportTemplateDef.getStartRowIndex();
                        // 通过mapList进行excle赋值
                        if (mapList.size() > 0) {
                            for (int j = 0; j < mapList.size(); j++) {
                                HSSFRow row = sheet.createRow(rowIndex);
                                Map map = mapList.get(j);
                                HSSFCell cell = row.createCell(0);
                                sheet.autoSizeColumn(0, true);
                                cell.setCellValue(j + 1);
                                int k = 1;
                                for (Iterator it = map.keySet().iterator(); it
                                        .hasNext(); ) {
                                    String key = (String) it.next();
                                    cell = row.createCell(k);
                                    sheet.autoSizeColumn(k, true);
                                    if(exportTemplateDef.getSheetName().equals("40.计量箱")&& key.equals("t2")){
                                        String value=map.get(key).toString();
                                        if(value==null){
                                            continue;
                                        }
                                        if(value.contains("#杆")){
                                            value=value.substring(value.lastIndexOf("#杆")+2);
                                        }
                                        if(value.contains("表箱")){
                                            value=value.substring(value.lastIndexOf("表箱")+2);
                                        }
                                        if(value.contains("#")){
                                            value=value.substring(value.lastIndexOf("#")+1);
                                        }

                                        cell.setCellValue(value);
                                    }
                                    else {
                                        cell.setCellValue((map.get(key) == null || map.get(key).equals("null") || map.get(key).equals("NULL")) ? "" : map.get(key) + "");
                                    }
                                    k++;
                                }
                                rowIndex++;
                            }
                        } else {
                            _logger.info("jdbcTemplate.queryForList(" + sheetSql
                                    + "):mapList.size() =0");
                            if ((i + 1) == mapList.size()) {
                                return returnCode;
                            }
                        }
                    }
                    // 读取输出文件路径并输出excle
                    String dir = "";
                    String fileName = "";
                    List<Config> exportDataPathList = configMapper
                            .selectByKey("ExportDataPath");
                    if (exportDataPathList != null && exportDataPathList.size() > 0) {
                        dir = exportDataPathList.get(0).getConfigValue();
                        dir = dir.replace("@COMPANYID", String.valueOf(companyId));
                        dir = dir.replace("@TIMESTAMP", String.valueOf(System
                                .currentTimeMillis()));
                    }
                    // 临时存放图片文件夹
                    //String tempstr = "D:\\" + System.currentTimeMillis();
                    File fdir = new File(dir);
                    if (!fdir.exists()) {
                        fdir.mkdirs();
                    }
                    List<Config> exportExcelNameList = configMapper
                            .selectByKey("YXGISExportName");
                    if (exportExcelNameList != null
                            && exportExcelNameList.size() > 0) {
                        fileName = exportExcelNameList.get(0).getConfigValue();
                    }
                    _logger.info("dir=" + dir);
                    _logger.info("fileName=" + fileName);
                    FileOutputStream os = null;
                    try {
                        //os = new FileOutputStream(dir + "\\test1.xls");
                        os = new FileOutputStream(dir + fileName);
                        wb.write(os);
                        os.close();
                        returnCode = dir + fileName;
                    } catch (FileNotFoundException e) {
                        e.printStackTrace();
                    } catch (IOException e) {
                        e.printStackTrace();
                    } finally {
                        try {
                            os.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                } else {
                    _logger
                            .info("exportTemplateDefMapper.selectTemplateDefByTemplateId("
                                    + templateId
                                    + "):exportTemplateDefList.size() =0");
                }
            } else {
                _logger.info("exportTemplateMapper.selectTemplateByIdAndStatus("
                        + templateId + ",1):is null");
            }

            _logger.info("returnCode =======" + returnCode);
        } catch (Exception e) {
            _logger.info(e);
            e.printStackTrace();
        }
        return returnCode;
    }

    @Override
    public String exportPic(long companyId, String stratTime, String endTime, String tempDir, int type)
            throws PwtzDatabaseException {
        _logger.info("exportPic =====================");
        // 返回成功失败表示，1为成功，2为失败
        String returnCode = "2";
        List<Pdbyq> allList = new ArrayList<Pdbyq>();
        List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>(
                0);
        String sql = "";
        // 查询配电变压器所属图片
        sql = "select a.DeviceName,a.DeviceNumber,a.GISCode,a.OperationUnit,a.MaintenanceTeam,b.pname,b.DeviceType from T_PDBYQ a,"
                + "(SELECT DeviceID,group_concat(PhotoName) pname,DeviceType FROM T_KZSXSBZP WHERE date_format(CreateDate, '%Y%m%d')>= '"
                + stratTime
                + "' AND "
                + "date_format(CreateDate, '%Y%m%d')<= '"
                + endTime
                + "' AND CompanyID = "
                + companyId
                + " GROUP BY DeviceID) b where a.DeviceId=b.DeviceID order by b.DeviceType,a.DeviceName";
        try {
            mapList = jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (mapList.size() > 0) {
            for (int i = 0; i < mapList.size(); i++) {
                Pdbyq pdbyq = new Pdbyq();
                pdbyq.setDevicename((String) mapList.get(i).get("DeviceName"));
                pdbyq.setDevicenumber(mapList.get(i).get("DeviceNumber") + "");
                pdbyq.setGiscode((String) mapList.get(i).get("GISCode"));
                pdbyq.setNote(mapList.get(i).get("DeviceType") + "");
                pdbyq.setOperationunit((String) mapList.get(i).get(
                        "OperationUnit"));
                pdbyq.setMaintenanceteam((String) mapList.get(i).get(
                        "MaintenanceTeam"));
                // 图片名称
                pdbyq.setLocalcity((String) mapList.get(i).get("pname"));
                allList.add(pdbyq);
            }
        }
        // 查询柱上变压器所属图片
        sql = "select a.DeviceName,a.DeviceNumber,a.GISCode,a.OperationUnit,a.MaintenanceTeam,b.pname,b.DeviceType from T_ZSBYQ a,"
                + "(SELECT DeviceID,group_concat(PhotoName) pname,DeviceType FROM T_KZSXSBZP WHERE date_format(CreateDate, '%Y%m%d')>= '"
                + stratTime
                + "' AND "
                + "date_format(CreateDate, '%Y%m%d')<= '"
                + endTime
                + "' AND CompanyID = "
                + companyId
                + " GROUP BY DeviceID) b where a.DeviceId=b.DeviceID order by b.DeviceType,a.DeviceName";
        try {
            mapList = jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (mapList.size() > 0) {
            for (int i = 0; i < mapList.size(); i++) {
                Pdbyq pdbyq = new Pdbyq();
                pdbyq.setDevicename((String) mapList.get(i).get("DeviceName"));
                pdbyq.setDevicenumber(mapList.get(i).get("DeviceNumber") + "");
                pdbyq.setGiscode((String) mapList.get(i).get("GISCode"));
                pdbyq.setNote(mapList.get(i).get("DeviceType") + "");
                pdbyq.setOperationunit((String) mapList.get(i).get(
                        "OperationUnit"));
                pdbyq.setMaintenanceteam((String) mapList.get(i).get(
                        "MaintenanceTeam"));
                // 图片名称
                pdbyq.setLocalcity((String) mapList.get(i).get("pname"));
                allList.add(pdbyq);
            }
        }
        // 查询低压塔杆所属图片
        sql = "select a.DeviceName,a.DeviceNumber,a.GISCode,a.OperationUnit,a.MaintenanceTeam,b.pname,b.DeviceType from T_DYGT a,"
                + "(SELECT DeviceID,group_concat(PhotoName) pname,DeviceType FROM T_KZSXSBZP WHERE date_format(CreateDate, '%Y%m%d')>= '"
                + stratTime
                + "' AND "
                + "date_format(CreateDate, '%Y%m%d')<= '"
                + endTime
                + "' AND CompanyID = "
                + companyId
                + " GROUP BY DeviceID) b where a.DeviceId=b.DeviceID order by b.DeviceType,a.DeviceName";
        try {
            mapList = jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (mapList.size() > 0) {
            for (int i = 0; i < mapList.size(); i++) {
                Pdbyq pdbyq = new Pdbyq();
                pdbyq.setDevicename((String) mapList.get(i).get("DeviceName"));
                pdbyq.setDevicenumber(mapList.get(i).get("DeviceNumber") + "");
                pdbyq.setGiscode((String) mapList.get(i).get("GISCode"));
                pdbyq.setNote(mapList.get(i).get("DeviceType") + "");
                pdbyq.setOperationunit((String) mapList.get(i).get(
                        "OperationUnit"));
                pdbyq.setMaintenanceteam((String) mapList.get(i).get(
                        "MaintenanceTeam"));
                // 图片名称
                pdbyq.setLocalcity((String) mapList.get(i).get("pname"));
                allList.add(pdbyq);
            }
        }
        // 查询低压配电箱所属图片
        sql = "select a.DeviceName,a.DeviceNumber,a.GISCode,a.OperationUnit,a.MaintenanceTeam,b.pname,b.DeviceType from T_DYPDX a,"
                + "(SELECT DeviceID,group_concat(PhotoName) pname,DeviceType FROM T_KZSXSBZP WHERE date_format(CreateDate, '%Y%m%d')>= '"
                + stratTime
                + "' AND "
                + "date_format(CreateDate, '%Y%m%d')<= '"
                + endTime
                + "' AND CompanyID = "
                + companyId
                + " GROUP BY DeviceID) b where a.DeviceId=b.DeviceID order by b.DeviceType,a.DeviceName";
        try {
            mapList = jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (mapList.size() > 0) {
            for (int i = 0; i < mapList.size(); i++) {
                Pdbyq pdbyq = new Pdbyq();
                pdbyq.setDevicename((String) mapList.get(i).get("DeviceName"));
                pdbyq.setDevicenumber(mapList.get(i).get("DeviceNumber") + "");
                pdbyq.setGiscode((String) mapList.get(i).get("GISCode"));
                pdbyq.setNote(mapList.get(i).get("DeviceType") + "");
                pdbyq.setOperationunit((String) mapList.get(i).get(
                        "OperationUnit"));
                pdbyq.setMaintenanceteam((String) mapList.get(i).get(
                        "MaintenanceTeam"));
                // 图片名称
                pdbyq.setLocalcity((String) mapList.get(i).get("pname"));
                allList.add(pdbyq);
            }
        }
        // 查询低压电缆分接箱所属图片
        sql = "select a.DeviceName,a.DeviceNumber,a.GISCode,a.OperationUnit,a.MaintenanceTeam,b.pname,b.DeviceType from T_DYDLFJX a,"
                + "(SELECT DeviceID,group_concat(PhotoName) pname,DeviceType FROM T_KZSXSBZP WHERE date_format(CreateDate, '%Y%m%d')>= '"
                + stratTime
                + "' AND "
                + "date_format(CreateDate, '%Y%m%d')<= '"
                + endTime
                + "' AND CompanyID = "
                + companyId
                + " GROUP BY DeviceID) b where a.DeviceId=b.DeviceID order by b.DeviceType,a.DeviceName";
        try {
            mapList = jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (mapList.size() > 0) {
            for (int i = 0; i < mapList.size(); i++) {
                Pdbyq pdbyq = new Pdbyq();
                pdbyq.setDevicename((String) mapList.get(i).get("DeviceName"));
                pdbyq.setDevicenumber(mapList.get(i).get("DeviceNumber") + "");
                pdbyq.setGiscode((String) mapList.get(i).get("GISCode"));
                pdbyq.setNote(mapList.get(i).get("DeviceType") + "");
                pdbyq.setOperationunit((String) mapList.get(i).get(
                        "OperationUnit"));
                pdbyq.setMaintenanceteam((String) mapList.get(i).get(
                        "MaintenanceTeam"));
                // 图片名称
                pdbyq.setLocalcity((String) mapList.get(i).get("pname"));
                allList.add(pdbyq);
            }
        }
        _logger.info("allList size=======" + allList.size());
        // 生成EXCLE
        if (allList.size() > 0) {
            // 创建Excel的工作书册 Workbook,对应到一个excel文档
            HSSFWorkbook wb = new HSSFWorkbook();
            // 创建Excel的工作sheet,对应到一个excel文档的tab
            HSSFSheet sheet = wb.createSheet("配电-电缆终端头");
            // 创建Excel的sheet的标题行
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);
            sheet.autoSizeColumn(0, true);
            cell.setCellValue("设备名称");
            cell = row.createCell(1);
            sheet.autoSizeColumn(1, true);
            cell.setCellValue("设备编号");
            cell = row.createCell(2);
            sheet.autoSizeColumn(2, true);
            cell.setCellValue("业务系统ID");
            cell = row.createCell(3);
            sheet.autoSizeColumn(3, true);
            cell.setCellValue("类型ID");
            cell = row.createCell(4);
            sheet.autoSizeColumn(4, true);
            cell.setCellValue("运行单位");
            cell = row.createCell(5);
            sheet.autoSizeColumn(5, true);
            cell.setCellValue("维护班组");
            cell = row.createCell(6);
            sheet.autoSizeColumn(6, true);
            cell.setCellValue("附件");
            for (int i = 0; i < allList.size(); i++) {
                row = sheet.createRow(i + 1);
                Pdbyq pdbyq = allList.get(i);
                cell = row.createCell(0);
                sheet.autoSizeColumn(0, true);
                cell.setCellValue(pdbyq.getDevicename());
                cell = row.createCell(1);
                sheet.autoSizeColumn(1, true);
                cell.setCellValue(pdbyq.getGiscode());
                cell = row.createCell(2);
                sheet.autoSizeColumn(2, true);
                cell.setCellValue(pdbyq.getDevicenumber());
                cell = row.createCell(3);
                sheet.autoSizeColumn(3, true);
                cell.setCellValue(pdbyq.getNote());
                cell = row.createCell(4);
                sheet.autoSizeColumn(4, true);
                cell.setCellValue(pdbyq.getOperationunit());
                cell = row.createCell(5);
                sheet.autoSizeColumn(5, true);
                cell.setCellValue(pdbyq.getMaintenanceteam());
                cell = row.createCell(6);
                sheet.autoSizeColumn(6, true);
                String picname = pdbyq.getLocalcity().replaceAll(",", ";");
                _logger.info("picname==============" + picname);
                String[] picarray = picname.split(";");
                _logger.info("picarray==============" + picarray.length);
                String newpicname = "";
                for (int j = 0; j < picarray.length; j++) {
                    if (picarray[j].indexOf("_") > 0) {
                        newpicname += picarray[j].substring(0, picarray[j].lastIndexOf("_")) + picarray[j].substring(picarray[j].lastIndexOf("."), picarray[j].length()) + ";";
                    } else {
                        newpicname += picarray[j] + ";";
                    }
                }
                _logger.info("newpicname sbustring==============" + newpicname);
                cell.setCellValue(newpicname);
            }
            FileOutputStream os = null;
            try {
                if (type == 2) {
                    File file = new File(tempDir);
                    if (!file.exists()) {
                        file.mkdirs();
                    }
                }
                os = new FileOutputStream(tempDir + "照片模板数据.xls");
                wb.write(os);
                os.close();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
                return returnCode;
            } catch (IOException e) {
                e.printStackTrace();
                return returnCode;
            } finally {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            // 查询图片数据
            /*sql = "select PhotoName from T_KZSXSBZP WHERE date_format(CreateDate, '%Y%m%d')>= '"
					+ stratTime
					+ "' AND "
					+ "date_format(CreateDate, '%Y%m%d')<= '"
					+ endTime
					+ "' AND CompanyID = " + companyId;
			mapList = jdbcTemplate.queryForList(sql);*/
            if (allList.size() > 0) {
                File tempdir = new File(tempDir);
                if (!tempdir.exists() && type == 0) {
                    return returnCode;
                } else {
                    tempdir.mkdirs();
                }
                String picdir = "";
                List<Config> devicePICPathList = configMapper.selectByKey("DevicePICPath");
                if (devicePICPathList != null && devicePICPathList.size() > 0) {
                    picdir = devicePICPathList.get(0).getConfigValue();
                    picdir = picdir.replace("@COMPANYID", String.valueOf(companyId));
                }
                for (int i = 0; i < allList.size(); i++) {
                    String[] photoarray = allList.get(i).getLocalcity().replaceAll(",", ";").split(";");
                    for (int j = 0; j < photoarray.length; j++) {
                        String newpicname = "";
                        String picname = photoarray[j];
                        if (picname.indexOf("_") > 0) {
                            newpicname = picname.substring(0, picname.lastIndexOf("_")) + picname.substring(picname.lastIndexOf("."), picname.length());
                        } else {
                            newpicname = picname;
                        }
                        String photoUrl = picdir + photoarray[j];
                        File photodir = new File(photoUrl);
                        if (!photodir.exists()) {
                            continue;
                        }
                        FileInputStream fi = null;
                        FileOutputStream fo = null;
                        FileChannel in = null;
                        FileChannel out = null;
                        try {
                            fi = new FileInputStream(photoUrl);
                            fo = new FileOutputStream(tempDir + newpicname);
                            in = fi.getChannel();// 得到对应的文件通道
                            out = fo.getChannel();// 得到对应的文件通道
                            in.transferTo(0, in.size(), out);
                        } catch (Exception e) {
                            e.printStackTrace();
                            return returnCode;
                        } finally {
                            try {
                                fi.close();
                                fo.close();
                                in.close();
                                out.close();
                            } catch (IOException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
                // 压缩文件夹
                String dir = "";
                String fileName = "";
                List<Config> exportDataPathList = configMapper
                        .selectByKey("ExportDataPath");
                if (exportDataPathList != null && exportDataPathList.size() > 0) {
                    dir = exportDataPathList.get(0).getConfigValue();
                    dir = dir.replace("@COMPANYID", String.valueOf(companyId));
                    dir = dir.replace("@TIMESTAMP", String.valueOf(System
                            .currentTimeMillis()));
                }
                File fdir = new File(dir);
                if (!fdir.exists()) {
                    fdir.mkdirs();
                }
                List<Config> exportZIPNameList = configMapper.selectByKey("ExportZIPName");
                if (exportZIPNameList != null && exportZIPNameList.size() > 0) {
                    fileName = exportZIPNameList.get(0).getConfigValue();
                }
                _logger.info("dir=" + dir);
                _logger.info("fileName=" + fileName);
                try {
                    zip(dir + fileName, tempDir);
                    deleteFile(tempdir);
                    returnCode = dir + fileName;
                } catch (Exception e) {
                    e.printStackTrace();
                }
            } else {
                _logger.info("select PhotoName:allList.size() =0");
            }
        } else {
            _logger.info("exportPic:allList.size() =0");
            List<Config> exportExcelNameList = configMapper
                    .selectByKey("ExportExcelName");
            if (exportExcelNameList != null
                    && exportExcelNameList.size() > 0 && type == 0) {
                returnCode = tempDir + exportExcelNameList.get(0).getConfigValue();
            } else {
                return returnCode = "1";
            }
        }

        _logger.info("pic returnCode=" + returnCode);
        return returnCode;
    }

    @Override
    public String exportGISPic(long companyId, String stratTime, String endTime, String tempDir, int type)
            throws PwtzDatabaseException {
        _logger.info("exportPic =====================");
        // 返回成功失败表示，1为成功，2为失败
        String returnCode = "2";
        List<Pdbyq> allList = new ArrayList<Pdbyq>();
        List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>(
                0);
        String sql = "";
        // 查询配电变压器所属图片
        sql = "select a.DeviceName,a.DeviceNumber,a.GISCode,a.OperationUnit,a.MaintenanceTeam,b.pname,b.DeviceType from T_PDBYQ a,"
                + "(SELECT DeviceID,group_concat(PhotoName) pname,DeviceType FROM T_KZSXSBZP WHERE date_format(CreateDate, '%Y%m%d')>= '"
                + stratTime
                + "' AND "
                + "date_format(CreateDate, '%Y%m%d')<= '"
                + endTime
                + "' AND CompanyID = "
                + companyId
                + " GROUP BY DeviceID) b where a.DeviceId=b.DeviceID and a.GISCode !='' order by b.DeviceType,a.DeviceName";
        try {
            mapList = jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (mapList.size() > 0) {
            for (int i = 0; i < mapList.size(); i++) {
                Pdbyq pdbyq = new Pdbyq();
                pdbyq.setDevicename((String) mapList.get(i).get("DeviceName"));
                pdbyq.setDevicenumber(mapList.get(i).get("DeviceNumber") + "");
                pdbyq.setGiscode((String) mapList.get(i).get("GISCode"));
                pdbyq.setNote(mapList.get(i).get("DeviceType") + "");
                pdbyq.setOperationunit((String) mapList.get(i).get(
                        "OperationUnit"));
                pdbyq.setMaintenanceteam((String) mapList.get(i).get(
                        "MaintenanceTeam"));
                // 图片名称
                pdbyq.setLocalcity((String) mapList.get(i).get("pname"));
                allList.add(pdbyq);
            }
        }
        // 查询柱上变压器所属图片
        sql = "select a.DeviceName,a.DeviceNumber,a.GISCode,a.OperationUnit,a.MaintenanceTeam,b.pname,b.DeviceType from T_ZSBYQ a,"
                + "(SELECT DeviceID,group_concat(PhotoName) pname,DeviceType FROM T_KZSXSBZP WHERE date_format(CreateDate, '%Y%m%d')>= '"
                + stratTime
                + "' AND "
                + "date_format(CreateDate, '%Y%m%d')<= '"
                + endTime
                + "' AND CompanyID = "
                + companyId
                + " GROUP BY DeviceID) b where a.DeviceId=b.DeviceID and a.GISCode !='' order by b.DeviceType,a.DeviceName";
        try {
            mapList = jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (mapList.size() > 0) {
            for (int i = 0; i < mapList.size(); i++) {
                Pdbyq pdbyq = new Pdbyq();
                pdbyq.setDevicename((String) mapList.get(i).get("DeviceName"));
                pdbyq.setDevicenumber(mapList.get(i).get("DeviceNumber") + "");
                pdbyq.setGiscode((String) mapList.get(i).get("GISCode"));
                pdbyq.setNote(mapList.get(i).get("DeviceType") + "");
                pdbyq.setOperationunit((String) mapList.get(i).get(
                        "OperationUnit"));
                pdbyq.setMaintenanceteam((String) mapList.get(i).get(
                        "MaintenanceTeam"));
                // 图片名称
                pdbyq.setLocalcity((String) mapList.get(i).get("pname"));
                allList.add(pdbyq);
            }
        }
        // 查询低压塔杆所属图片
        sql = "select a.DeviceName,a.DeviceNumber,a.GISCode,a.OperationUnit,a.MaintenanceTeam,b.pname,b.DeviceType from T_DYGT a,"
                + "(SELECT DeviceID,group_concat(PhotoName) pname,DeviceType FROM T_KZSXSBZP WHERE date_format(CreateDate, '%Y%m%d')>= '"
                + stratTime
                + "' AND "
                + "date_format(CreateDate, '%Y%m%d')<= '"
                + endTime
                + "' AND CompanyID = "
                + companyId
                + " GROUP BY DeviceID) b where a.DeviceId=b.DeviceID and a.GISCode !='' order by b.DeviceType,a.DeviceName";
        try {
            mapList = jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (mapList.size() > 0) {
            for (int i = 0; i < mapList.size(); i++) {
                Pdbyq pdbyq = new Pdbyq();
                pdbyq.setDevicename((String) mapList.get(i).get("DeviceName"));
                pdbyq.setDevicenumber(mapList.get(i).get("DeviceNumber") + "");
                pdbyq.setGiscode((String) mapList.get(i).get("GISCode"));
                pdbyq.setNote(mapList.get(i).get("DeviceType") + "");
                pdbyq.setOperationunit((String) mapList.get(i).get(
                        "OperationUnit"));
                pdbyq.setMaintenanceteam((String) mapList.get(i).get(
                        "MaintenanceTeam"));
                // 图片名称
                pdbyq.setLocalcity((String) mapList.get(i).get("pname"));
                allList.add(pdbyq);
            }
        }
        // 查询低压配电箱所属图片
        sql = "select a.DeviceName,a.DeviceNumber,a.GISCode,a.OperationUnit,a.MaintenanceTeam,b.pname,b.DeviceType from T_DYPDX a,"
                + "(SELECT DeviceID,group_concat(PhotoName) pname,DeviceType FROM T_KZSXSBZP WHERE date_format(CreateDate, '%Y%m%d')>= '"
                + stratTime
                + "' AND "
                + "date_format(CreateDate, '%Y%m%d')<= '"
                + endTime
                + "' AND CompanyID = "
                + companyId
                + " GROUP BY DeviceID) b where a.DeviceId=b.DeviceID and a.GISCode !='' order by b.DeviceType,a.DeviceName";
        try {
            mapList = jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (mapList.size() > 0) {
            for (int i = 0; i < mapList.size(); i++) {
                Pdbyq pdbyq = new Pdbyq();
                pdbyq.setDevicename((String) mapList.get(i).get("DeviceName"));
                pdbyq.setDevicenumber(mapList.get(i).get("DeviceNumber") + "");
                pdbyq.setGiscode((String) mapList.get(i).get("GISCode"));
                pdbyq.setNote(mapList.get(i).get("DeviceType") + "");
                pdbyq.setOperationunit((String) mapList.get(i).get(
                        "OperationUnit"));
                pdbyq.setMaintenanceteam((String) mapList.get(i).get(
                        "MaintenanceTeam"));
                // 图片名称
                pdbyq.setLocalcity((String) mapList.get(i).get("pname"));
                allList.add(pdbyq);
            }
        }
        // 查询低压电缆分接箱所属图片
        sql = "select a.DeviceName,a.DeviceNumber,a.GISCode,a.OperationUnit,a.MaintenanceTeam,b.pname,b.DeviceType from T_DYDLFJX a,"
                + "(SELECT DeviceID,group_concat(PhotoName) pname,DeviceType FROM T_KZSXSBZP WHERE date_format(CreateDate, '%Y%m%d')>= '"
                + stratTime
                + "' AND "
                + "date_format(CreateDate, '%Y%m%d')<= '"
                + endTime
                + "' AND CompanyID = "
                + companyId
                + " GROUP BY DeviceID) b where a.DeviceId=b.DeviceID and a.GISCode !='' order by b.DeviceType,a.DeviceName";
        try {
            mapList = jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (mapList.size() > 0) {
            for (int i = 0; i < mapList.size(); i++) {
                Pdbyq pdbyq = new Pdbyq();
                pdbyq.setDevicename((String) mapList.get(i).get("DeviceName"));
                pdbyq.setDevicenumber(mapList.get(i).get("DeviceNumber") + "");
                pdbyq.setGiscode((String) mapList.get(i).get("GISCode"));
                pdbyq.setNote(mapList.get(i).get("DeviceType") + "");
                pdbyq.setOperationunit((String) mapList.get(i).get(
                        "OperationUnit"));
                pdbyq.setMaintenanceteam((String) mapList.get(i).get(
                        "MaintenanceTeam"));
                // 图片名称
                pdbyq.setLocalcity((String) mapList.get(i).get("pname"));
                allList.add(pdbyq);
            }
        }
        _logger.info("allList size=======" + allList.size());
        // 生成EXCLE
        if (allList.size() > 0) {
            // 创建Excel的工作书册 Workbook,对应到一个excel文档
            HSSFWorkbook wb = new HSSFWorkbook();
            // 创建Excel的工作sheet,对应到一个excel文档的tab
            HSSFSheet sheet = wb.createSheet("配电-电缆终端头");
            // 创建Excel的sheet的标题行
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);
            sheet.autoSizeColumn(0, true);
            cell.setCellValue("设备名称");
            cell = row.createCell(1);
            sheet.autoSizeColumn(1, true);
            cell.setCellValue("设备编号");
            cell = row.createCell(2);
            sheet.autoSizeColumn(2, true);
            cell.setCellValue("业务系统ID");
            cell = row.createCell(3);
            sheet.autoSizeColumn(3, true);
            cell.setCellValue("类型ID");
            cell = row.createCell(4);
            sheet.autoSizeColumn(4, true);
            cell.setCellValue("运行单位");
            cell = row.createCell(5);
            sheet.autoSizeColumn(5, true);
            cell.setCellValue("维护班组");
            cell = row.createCell(6);
            sheet.autoSizeColumn(6, true);
            cell.setCellValue("附件");
            for (int i = 0; i < allList.size(); i++) {
                row = sheet.createRow(i + 1);
                Pdbyq pdbyq = allList.get(i);
                cell = row.createCell(0);
                sheet.autoSizeColumn(0, true);
                cell.setCellValue(pdbyq.getDevicename());
                cell = row.createCell(1);
                sheet.autoSizeColumn(1, true);
                cell.setCellValue(pdbyq.getGiscode());
                cell = row.createCell(2);
                sheet.autoSizeColumn(2, true);
                cell.setCellValue(pdbyq.getDevicenumber());
                cell = row.createCell(3);
                sheet.autoSizeColumn(3, true);
                cell.setCellValue(pdbyq.getNote());
                cell = row.createCell(4);
                sheet.autoSizeColumn(4, true);
                cell.setCellValue(pdbyq.getOperationunit());
                cell = row.createCell(5);
                sheet.autoSizeColumn(5, true);
                cell.setCellValue(pdbyq.getMaintenanceteam());
                cell = row.createCell(6);
                sheet.autoSizeColumn(6, true);
                String picname = pdbyq.getLocalcity().replaceAll(",", ";");
                _logger.info("picname==============" + picname);
                String[] picarray = picname.split(";");
                _logger.info("picarray==============" + picarray.length);
                String newpicname = "";
                for (int j = 0; j < picarray.length; j++) {
                    if (picarray[j].indexOf("_") > 0) {
                        newpicname += picarray[j].substring(0, picarray[j].lastIndexOf("_")) + picarray[j].substring(picarray[j].lastIndexOf("."), picarray[j].length()) + ";";
                    } else {
                        newpicname += picarray[j] + ";";
                    }
                }
                _logger.info("newpicname sbustring==============" + newpicname);
                cell.setCellValue(newpicname);
            }
            FileOutputStream os = null;
            try {
                if (type == 3) {
                    File file = new File(tempDir);
                    if (!file.exists()) {
                        file.mkdirs();
                    }
                }
                os = new FileOutputStream(tempDir + "照片模板数据.xls");
                wb.write(os);
                os.close();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
                return returnCode;
            } catch (IOException e) {
                e.printStackTrace();
                return returnCode;
            } finally {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            // 查询图片数据
            /*sql = "select PhotoName from T_KZSXSBZP WHERE date_format(CreateDate, '%Y%m%d')>= '"
                    + stratTime
                    + "' AND "
                    + "date_format(CreateDate, '%Y%m%d')<= '"
                    + endTime
                    + "' AND CompanyID = " + companyId;
            mapList = jdbcTemplate.queryForList(sql);*/
            if (allList.size() > 0) {
                File tempdir = new File(tempDir);
                if (!tempdir.exists() && type == 0) {
                    return returnCode;
                } else {
                    tempdir.mkdirs();
                }
                String picdir = "";
                List<Config> devicePICPathList = configMapper.selectByKey("DevicePICPath");
                if (devicePICPathList != null && devicePICPathList.size() > 0) {
                    picdir = devicePICPathList.get(0).getConfigValue();
                    picdir = picdir.replace("@COMPANYID", String.valueOf(companyId));
                }
                for (int i = 0; i < allList.size(); i++) {
                    String[] photoarray = allList.get(i).getLocalcity().replaceAll(",", ";").split(";");
                    for (int j = 0; j < photoarray.length; j++) {
                        String newpicname = "";
                        String picname = photoarray[j];
                        if (picname.indexOf("_") > 0) {
                            newpicname = picname.substring(0, picname.lastIndexOf("_")) + picname.substring(picname.lastIndexOf("."), picname.length());
                        } else {
                            newpicname = picname;
                        }
                        String photoUrl = picdir + photoarray[j];
                        File photodir = new File(photoUrl);
                        if (!photodir.exists()) {
                            continue;
                        }
                        FileInputStream fi = null;
                        FileOutputStream fo = null;
                        FileChannel in = null;
                        FileChannel out = null;
                        try {
                            fi = new FileInputStream(photoUrl);
                            fo = new FileOutputStream(tempDir + newpicname);
                            in = fi.getChannel();// 得到对应的文件通道
                            out = fo.getChannel();// 得到对应的文件通道
                            in.transferTo(0, in.size(), out);
                        } catch (Exception e) {
                            e.printStackTrace();
                            return returnCode;
                        } finally {
                            try {
                                fi.close();
                                fo.close();
                                in.close();
                                out.close();
                            } catch (IOException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
                // 压缩文件夹
                String dir = "";
                String fileName = "";
                List<Config> exportDataPathList = configMapper
                        .selectByKey("ExportDataPath");
                if (exportDataPathList != null && exportDataPathList.size() > 0) {
                    dir = exportDataPathList.get(0).getConfigValue();
                    dir = dir.replace("@COMPANYID", String.valueOf(companyId));
                    dir = dir.replace("@TIMESTAMP", String.valueOf(System
                            .currentTimeMillis()));
                }
                File fdir = new File(dir);
                if (!fdir.exists()) {
                    fdir.mkdirs();
                }
                List<Config> exportZIPNameList = configMapper.selectByKey("ExportZIPName");
                if (exportZIPNameList != null && exportZIPNameList.size() > 0) {
                    fileName = exportZIPNameList.get(0).getConfigValue();
                }
                _logger.info("dir=" + dir);
                _logger.info("fileName=" + fileName);
                try {
                    zip(dir + fileName, tempDir);
                    deleteFile(tempdir);
                    returnCode = dir + fileName;
                } catch (Exception e) {
                    e.printStackTrace();
                }
            } else {
                _logger.info("select PhotoName:allList.size() =0");
            }
        } else {
            _logger.info("exportPic:allList.size() =0");
            List<Config> exportExcelNameList = configMapper
                    .selectByKey("ExportExcelName");
            if (exportExcelNameList != null
                    && exportExcelNameList.size() > 0 && type == 0) {
                returnCode = tempDir + exportExcelNameList.get(0).getConfigValue();
            } else {
                return returnCode = "1";
            }
        }

        _logger.info("pic returnCode=" + returnCode);
        return returnCode;
    }

    /**
     * inputFileName 输入一个文件夹 zipFileName 输出一个压缩文件夹
     **/
    private void zip(String zipFileName, String inputFileName) throws Exception {
        zip(zipFileName, new File(inputFileName));
    }

    private void zip(String zipFileName, File inputFile) {
        ZipOutputStream out = null;
        try {
            out = new ZipOutputStream(new FileOutputStream(zipFileName));
            zip(out, inputFile, "");
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
                // out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private void zip(ZipOutputStream out, File f, String base) {
        if (f.isDirectory()) {
            File[] fl = f.listFiles();
            try {
                out.putNextEntry(new org.apache.tools.zip.ZipEntry(base + "/"));
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            base = base.length() == 0 ? "" : base + "/";
            for (int i = 0; i < fl.length; i++) {
                zip(out, fl[i], base + fl[i].getName());
            }
        } else {
            FileInputStream in = null;
            try {
                out.putNextEntry(new org.apache.tools.zip.ZipEntry(base));
                in = new FileInputStream(f);
                int b;
                while ((b = in.read()) != -1) {
                    out.write(b);
                }
                in.close();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                try {
                    in.close();
                    // out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 删除目录（文件夹）以及目录下的文件
     *
     * @param file 被删除目录的文件路径
     */
    private void deleteFile(File file) {
        if (file.exists()) {
            if (file.isFile()) {
                file.delete();
            } else if (file.isDirectory()) {
                File files[] = file.listFiles();
                for (int i = 0; i < files.length; i++) {
                    this.deleteFile(files[i]);
                }
            }
            file.delete();
        } else {
            _logger.info("所删除的文件不存在！" + '\n');
        }
    }

    private int letterIntoDigitalByOne(String letter) {
        int returnCode = 0;
        letter = letter.toUpperCase();
        if ("A".equals(letter)) {
            returnCode = 1;
        } else if ("B".equals(letter)) {
            returnCode = 2;
        } else if ("C".equals(letter)) {
            returnCode = 3;
        } else if ("D".equals(letter)) {
            returnCode = 4;
        } else if ("E".equals(letter)) {
            returnCode = 5;
        } else if ("F".equals(letter)) {
            returnCode = 6;
        } else if ("G".equals(letter)) {
            returnCode = 7;
        } else if ("H".equals(letter)) {
            returnCode = 8;
        } else if ("I".equals(letter)) {
            returnCode = 9;
        } else if ("J".equals(letter)) {
            returnCode = 10;
        } else if ("K".equals(letter)) {
            returnCode = 11;
        } else if ("L".equals(letter)) {
            returnCode = 12;
        } else if ("M".equals(letter)) {
            returnCode = 13;
        } else if ("N".equals(letter)) {
            returnCode = 14;
        } else if ("O".equals(letter)) {
            returnCode = 15;
        } else if ("P".equals(letter)) {
            returnCode = 16;
        } else if ("Q".equals(letter)) {
            returnCode = 17;
        } else if ("R".equals(letter)) {
            returnCode = 18;
        } else if ("S".equals(letter)) {
            returnCode = 19;
        } else if ("T".equals(letter)) {
            returnCode = 20;
        } else if ("U".equals(letter)) {
            returnCode = 21;
        } else if ("V".equals(letter)) {
            returnCode = 22;
        } else if ("W".equals(letter)) {
            returnCode = 23;
        } else if ("X".equals(letter)) {
            returnCode = 24;
        } else if ("Y".equals(letter)) {
            returnCode = 25;
        } else if ("Z".equals(letter)) {
            returnCode = 26;
        }

        return returnCode;
    }

    private int letterIntoDigitalByTwo(String letter) {
        int returnCode = 0;
        letter = letter.toUpperCase();
        String one = letter.substring(0, 1);
        String two = letter.substring(1, letter.length());
        returnCode = letterIntoDigitalByOne(one) * 26
                + letterIntoDigitalByOne(two);

        return returnCode;
    }

    @Override
    public List<ExportTemplate> selectTemplate() throws PwtzDatabaseException {
        List<ExportTemplate> list = null;
        try {
            list = exportTemplateMapper.selectTemplate();
        } catch (DataAccessException e) {
            _logger.error(ExceptionUtils.getStackTrace(e));
            throw new PwtzDatabaseException();
        }
        return list;
    }

    @Override
    public List<ImportTemplate> selectImportTemplate() throws PwtzDatabaseException {
        List<ImportTemplate> list = null;
        try {
            list = importTemplateMapper.selectTemplate();
        } catch (DataAccessException e) {
            _logger.error(ExceptionUtils.getStackTrace(e));
            throw new PwtzDatabaseException();
        }
        return list;
    }

    @Override
    public List<ImportTemplate> selectTemplateByDeviceTypeId(int deviceTypeId) throws PwtzDatabaseException {
        List<ImportTemplate> list = null;
        try {
            list = importTemplateMapper.selectTemplateByDeviceTypeId(deviceTypeId);
        } catch (DataAccessException e) {
            _logger.error(ExceptionUtils.getStackTrace(e));
            throw new PwtzDatabaseException();
        }
        return list;
    }

    @Override
    public Map import2007Excle(long companyId, long templateId, String templateUrl,
                               long createId) {
        Map<String, Object> returnMap = new HashMap<String, Object>();
        // 成功、新增、更新、失败失败行数变量
        int successNum = 0;
        int insertNum = 0;
        int updateNum = 0;
        List fuilureLineNum = new ArrayList();
        int returnCode = 2;
        String sql = "";
        String insertSql = "";
        String updateSql = "";
        List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
        XSSFWorkbook wb = new XSSFWorkbook();
        try {
            wb = new XSSFWorkbook(new FileInputStream(templateUrl));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            returnMap.put("success", successNum);
            return returnMap;
        } catch (IOException e) {
            e.printStackTrace();
            returnMap.put("success", successNum);
            return returnMap;
        }
        // 根据模板ID查询插入表名
        sql = "select distinct(TABLE_NAME),SHEET_NAME from T_IMPORT_TEMPLATE_DEF where TEMPLATE_ID="
                + templateId + " group by SHEET_NAME";
        mapList = jdbcTemplate.queryForList(sql);
        if (mapList.size() > 0) {
            for (int i = 0; i < mapList.size(); i++) {
                List<Map<String, Object>> sheetMapList = new ArrayList<Map<String, Object>>();
                String tableName = (String) mapList.get(i).get("TABLE_NAME");
                String sheetName = (String) mapList.get(i).get("SHEET_NAME");
                // 获取EXCLE数据
                try {
                    XSSFSheet sheet = wb.getSheet(sheetName);
                    for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                        Map<String, Object> sheetMap = new HashMap();
                        XSSFRow row = sheet.getRow(rowIndex);
                        if (row == null) {
                            continue;
                        } else {
                            if (row.getCell(0) == null || row.getCell(0).toString().trim().equals("")) {
                                continue;
                            } else {
                                int cellNum = row.getLastCellNum();
                                for (int j = 0; j < cellNum; j++) {
                                    XSSFCell cell = row.getCell(j);
                                    if (cell != null) {
                                        int cellIndex = cell.getColumnIndex();
                                        String cellValue = "";
                                        switch (cell.getCellType()) {
                                            case HSSFCell.CELL_TYPE_STRING:
                                                cellValue = cell.getStringCellValue();
                                                break;
                                            case HSSFCell.CELL_TYPE_NUMERIC:
                                                SimpleDateFormat sdf = new SimpleDateFormat(
                                                        "yyyyMMdd");// 格式化日期字符串
                                                if ("General".equals(cell.getCellStyle()
                                                        .getDataFormatString())) {
                                                    cellValue = new DecimalFormat("0").format(cell
                                                            .getNumericCellValue());
                                                } else {
                                                    cellValue = sdf
                                                            .format(HSSFDateUtil.getJavaDate(cell
                                                                    .getNumericCellValue()));
                                                }
                                                break;
                                            case HSSFCell.CELL_TYPE_FORMULA:
                                                if (!cell.getStringCellValue().equals("")) {
                                                    cellValue = cell.getStringCellValue();
                                                } else {
                                                    cellValue = cell.getNumericCellValue() + "";
                                                }
                                                break;
                                            case HSSFCell.CELL_TYPE_BLANK:
                                                break;
                                            case HSSFCell.CELL_TYPE_ERROR:
                                                break;
                                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                                cellValue = (cell.getBooleanCellValue() == true ? "Y"
                                                        : "N");
                                                break;
                                            default:
                                                break;
                                        }
                                        sheetMap.put(String.valueOf(cellIndex), cellValue);
                                    } else {
                                        sheetMap.put(String.valueOf(j), "");
                                    }
                                }
                                sheetMapList.add(sheetMap);
                            }
                        }
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    returnMap.put("success", successNum);
                    return returnMap;
                }
                if (sheetMapList.size() > 0) {
                    // 根据表名查询对应列数据
                    List<ImportTemplateDef> importTemplateDefList = importTemplateDefMapper
                            .selectTemplateDefByTableName(tableName, sheetName, templateId);
                    for (int k = 0; k < sheetMapList.size(); k++) {
                        if (importTemplateDefList.size() > 0) {
                            Map<String, Object> sheetMap = sheetMapList.get(k);
                            insertSql = "insert into "
                                    + tableName
                                    + "(DeviceId,CreateId,CompanyId,CreateDate,LastModifiedTime,Status,LastOperatorID";
                            Object[] insertObject = new Object[importTemplateDefList
                                    .size() + 7];
                            insertObject[0] = UUID.randomUUID().toString();
                            insertObject[1] = createId;
                            insertObject[2] = companyId;
                            SimpleDateFormat df = new SimpleDateFormat(
                                    "yyyy-MM-dd HH:mm:ss");// 设置日期格式
                            String nowDate = df.format(new Date());
                            insertObject[3] = nowDate;
                            insertObject[4] = nowDate;
                            insertObject[5] = "1";
                            insertObject[6] = createId;
                            String insertColumn = "";
                            String insertValue = "";
                            updateSql = "update " + tableName
                                    + " set LastModifiedTime=?,LastOperatorID=?,Status=1";
                            String updateValue = "";
                            Object[] updateObject = new Object[importTemplateDefList
                                    .size() + 2];
                            updateObject[0] = df.format(new Date());
                            updateObject[1] = createId;
                            // 判断导入表中是否已经存在数据
                            int tablePkIndex = letterIntoDigitalByOne(importTemplateDefList
                                    .get(0).getPkExcelColumnName()) - 1;
                            String tablePkValue = (String) sheetMap.get(String
                                    .valueOf(tablePkIndex));
                            sql = "select * from " + tableName
                                    + " where DeviceName='" + tablePkValue
                                    + "' and CompanyId=" + companyId;
                            List<Map<String, Object>> pKMapList = jdbcTemplate
                                    .queryForList(sql);
                            if (pKMapList.size() > 0) {
                                for (int j = 0; j < importTemplateDefList
                                        .size(); j++) {
                                    // excelCellValue表中EXCEL对应值
                                    String excelCellValue = "";
                                    int excelCellIndex = -1;
                                    if (importTemplateDefList.get(j)
                                            .getExcelColumnName().length() == 1) {
                                        excelCellIndex = letterIntoDigitalByOne(importTemplateDefList
                                                .get(j).getExcelColumnName()) - 1;
                                    } else {
                                        excelCellIndex = letterIntoDigitalByTwo(importTemplateDefList
                                                .get(j).getExcelColumnName()) - 1;
                                    }
                                    excelCellValue = (String) sheetMap
                                            .get(String.valueOf(excelCellIndex));
                                    // 判断字段转换SQL
                                    String translateSql = importTemplateDefList
                                            .get(j).getTranslateSql();
                                    if (translateSql != null
                                            && !"".equals(translateSql)
                                            && !"null".equals(translateSql)) {
                                        Map mapSqlParm = new HashMap();
                                        if (translateSql.contains(":DeviceName")) {
                                            mapSqlParm.put("DeviceName",
                                                    excelCellValue);
                                        }
                                        if (translateSql.contains(":DEVICE_TYPE_NAME")) {
                                            mapSqlParm.put("DEVICE_TYPE_NAME",
                                                    excelCellValue);
                                        }

                                        if (translateSql.contains(":CompanyId")) {
                                            mapSqlParm.put("CompanyId",
                                                    companyId);
                                        }
                                        List<Map<String, Object>> translateMapList = simpleJdbcTemplate
                                                .queryForList(translateSql,
                                                        mapSqlParm);
                                        if (translateMapList.size() > 0) {
                                            Map map = translateMapList.get(0);
                                            for (Iterator it = map.keySet()
                                                    .iterator(); it.hasNext(); ) {
                                                String key = (String) it.next();
                                                try {
                                                    excelCellValue = map
                                                            .get(key) + "";
                                                } catch (Exception e) {
                                                    e.printStackTrace();
                                                }
                                            }
                                        } else {
                                            excelCellValue = "";
                                        }
                                    }
                                    // 拼接update sql
                                    updateValue += ", "
                                            + importTemplateDefList.get(j)
                                            .getTableFieldName() + "=?";
                                    updateObject[j + 2] = excelCellValue.trim().equals("") ? null : excelCellValue;
                                }
                                updateSql = updateSql + updateValue
                                        + " where DeviceName='" + tablePkValue
                                        + "'";
                                try {
                                    returnCode = jdbcTemplate.update(updateSql, updateObject);
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                                if (returnCode == 1) {
                                    successNum++;
                                    updateNum++;
                                } else {
                                    fuilureLineNum.add(sheetName + ":第"
                                            + (k + 1) + "行");
                                }
                            } else {
                                for (int j = 0; j < importTemplateDefList
                                        .size(); j++) {
                                    // excelCellValue表中EXCEL对应值
                                    String excelCellValue = "";
                                    int excelCellIndex = -1;
                                    if (importTemplateDefList.get(j)
                                            .getExcelColumnName().length() == 1) {
                                        excelCellIndex = letterIntoDigitalByOne(importTemplateDefList
                                                .get(j).getExcelColumnName()) - 1;
                                    } else {
                                        excelCellIndex = letterIntoDigitalByTwo(importTemplateDefList
                                                .get(j).getExcelColumnName()) - 1;
                                    }
                                    excelCellValue = (String) sheetMap
                                            .get(String.valueOf(excelCellIndex));
                                    // 判断字段转换SQL
                                    String translateSql = importTemplateDefList
                                            .get(j).getTranslateSql();
                                    if (translateSql != null
                                            && !"".equals(translateSql)
                                            && !"null".equals(translateSql)) {
                                        Map mapSqlParm = new HashMap();
                                        if (translateSql.contains(":DeviceName")) {
                                            mapSqlParm.put("DeviceName",
                                                    excelCellValue);
                                        }
                                        if (translateSql.contains(":DEVICE_TYPE_NAME")) {
                                            mapSqlParm.put("DEVICE_TYPE_NAME",
                                                    excelCellValue);
                                        }

                                        if (translateSql.contains(":CompanyId")) {
                                            mapSqlParm.put("CompanyId",
                                                    companyId);
                                        }
                                        List<Map<String, Object>> translateMapList = simpleJdbcTemplate
                                                .queryForList(translateSql,
                                                        mapSqlParm);
                                        if (translateMapList.size() > 0) {
                                            Map map = translateMapList.get(0);
                                            for (Iterator it = map.keySet()
                                                    .iterator(); it.hasNext(); ) {
                                                String key = (String) it.next();
                                                excelCellValue = map
                                                        .get(key) + "";
                                            }
                                        } else {
                                            excelCellValue = "";
                                        }
                                    }
                                    // 拼接insert sql
                                    if (j == 0) {
                                        insertColumn += ","
                                                + importTemplateDefList.get(j)
                                                .getTableFieldName();
                                        insertValue += " values(?";
                                    } else if ((j + 1) == importTemplateDefList
                                            .size()) {
                                        insertColumn += ","
                                                + importTemplateDefList.get(j)
                                                .getTableFieldName()
                                                + ")";
                                        insertValue += ",?,?,?,?,?,?,?,?)";
                                    } else {
                                        insertColumn += ","
                                                + importTemplateDefList.get(j)
                                                .getTableFieldName();
                                        insertValue += ",?";
                                    }
                                    if (excelCellValue == null) {
                                        insertObject[j + 7] = null;
                                    } else {
                                        insertObject[j + 7] = excelCellValue.trim().equals("") ? null : excelCellValue;
                                    }
                                }
                                insertSql = insertSql + insertColumn
                                        + insertValue;
                                try {
                                    returnCode = jdbcTemplate.update(insertSql, insertObject);
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                                if (returnCode == 1) {
                                    successNum++;
                                    insertNum++;
                                } else {
                                    fuilureLineNum.add(sheetName + ":第"
                                            + (k + 1) + "行");
                                }
                            }
                        } else {
                            _logger
                                    .info("importExcle:importTemplateDefList.size() =0");
                            returnMap.put("success", successNum);
                            return returnMap;
                        }
                    }
                } else {
                    _logger.info("importExcle:sheetMapList.size() =0");
                    returnMap.put("success", successNum);
                    return returnMap;
                }
            }
        } else {
            _logger.info("importExcle:mapList.size() =0");
        }
        returnMap.put("success", successNum);
        returnMap.put("insert", insertNum);
        returnMap.put("update", updateNum);
        returnMap.put("failure", fuilureLineNum);
        return returnMap;
    }

    @Override
    public Map import2003Excle(long companyId, long templateId, String templateUrl,
                               long createId) {
        Map<String, Object> returnMap = new HashMap<String, Object>();
        try {
            // 成功、新增、更新、失败失败行数变量
            int successNum = 0;
            int insertNum = 0;
            int updateNum = 0;
            List fuilureLineNum = new ArrayList();
            int returnCode = 2;
            String sql = "";
            String insertSql = "";
            String updateSql = "";
            List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
            HSSFWorkbook wb = new HSSFWorkbook();
            try {
                wb = new HSSFWorkbook(new FileInputStream(templateUrl));
            } catch (FileNotFoundException e) {
                e.printStackTrace();
                returnMap.put("success", successNum);
                return returnMap;
            } catch (IOException e) {
                e.printStackTrace();
                returnMap.put("success", successNum);
                return returnMap;
            }
            // 根据模板ID查询插入表名
            sql = "select distinct(TABLE_NAME),SHEET_NAME from T_IMPORT_TEMPLATE_DEF where TEMPLATE_ID="
                    + templateId + " group by SHEET_NAME";
            mapList = jdbcTemplate.queryForList(sql);
            if (mapList.size() > 0) {
                for (int i = 0; i < mapList.size(); i++) {
                    List<Map<String, Object>> sheetMapList = new ArrayList<Map<String, Object>>();
                    String tableName = (String) mapList.get(i).get("TABLE_NAME");
                    String sheetName = (String) mapList.get(i).get("SHEET_NAME");
                    // 获取EXCLE数据
                    try {
                        HSSFSheet sheet = wb.getSheet(sheetName);
                        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                            Map<String, Object> sheetMap = new HashMap();
                            HSSFRow row = sheet.getRow(rowIndex);
                            if (row == null) {
                                continue;
                            } else {
                                if (row.getCell(0) == null || row.getCell(0).toString().trim().equals("")) {
                                    continue;
                                } else {
                                    int cellNum = row.getLastCellNum();
                                    for (int j = 0; j < cellNum; j++) {
                                        HSSFCell cell = row.getCell(j);
                                        if (cell != null) {
                                            int cellIndex = cell.getColumnIndex();
                                            String cellValue = "";
                                            switch (cell.getCellType()) {
                                                case HSSFCell.CELL_TYPE_STRING:
                                                    cellValue = cell.getStringCellValue();
                                                    break;
                                                case HSSFCell.CELL_TYPE_NUMERIC:
                                                    SimpleDateFormat sdf = new SimpleDateFormat(
                                                            "yyyyMMdd");// 格式化日期字符串
                                                    if ("General".equals(cell.getCellStyle()
                                                            .getDataFormatString())) {
                                                        cellValue = new DecimalFormat("0").format(cell
                                                                .getNumericCellValue());
                                                    } else {
                                                        cellValue = sdf
                                                                .format(HSSFDateUtil.getJavaDate(cell
                                                                        .getNumericCellValue()));
                                                    }
                                                    break;
                                                case HSSFCell.CELL_TYPE_FORMULA:
                                                    if (!cell.getStringCellValue().equals("")) {
                                                        cellValue = cell.getStringCellValue();
                                                    } else {
                                                        cellValue = cell.getNumericCellValue() + "";
                                                    }
                                                    break;
                                                case HSSFCell.CELL_TYPE_BLANK:
                                                    break;
                                                case HSSFCell.CELL_TYPE_ERROR:
                                                    break;
                                                case HSSFCell.CELL_TYPE_BOOLEAN:
                                                    cellValue = (cell.getBooleanCellValue() == true ? "Y"
                                                            : "N");
                                                    break;
                                                default:
                                                    break;
                                            }
                                            sheetMap.put(String.valueOf(cellIndex), cellValue);
                                        } else {
                                            sheetMap.put(String.valueOf(j), "");
                                        }
                                    }
                                    sheetMapList.add(sheetMap);
                                }
                            }
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        returnMap.put("success", successNum);
                        return returnMap;
                    }
                    _logger.info("sheetMapList.size========" + sheetMapList.size());
                    if (sheetMapList.size() > 0) {
                        // 根据表名查询对应列数据
                        List<ImportTemplateDef> importTemplateDefList = importTemplateDefMapper
                                .selectTemplateDefByTableName(tableName, sheetName, templateId);
                        for (int k = 0; k < sheetMapList.size(); k++) {
                            _logger.info("importTemplateDefList.size========" + importTemplateDefList.size());
                            if (importTemplateDefList.size() > 0) {
                                Map<String, Object> sheetMap = sheetMapList.get(k);
                                insertSql = "insert into "
                                        + tableName
                                        + "(DeviceId,CreateId,CompanyId,CreateDate,LastModifiedTime,Status,LastOperatorID";
                                Object[] insertObject = new Object[importTemplateDefList
                                        .size() + 7];
                                insertObject[0] = UUID.randomUUID().toString();
                                insertObject[1] = createId;
                                insertObject[2] = companyId;
                                SimpleDateFormat df = new SimpleDateFormat(
                                        "yyyy-MM-dd HH:mm:ss");// 设置日期格式
                                String nowDate = df.format(new Date());
                                insertObject[3] = nowDate;
                                insertObject[4] = nowDate;
                                insertObject[5] = "1";
                                insertObject[6] = createId;
                                String insertColumn = "";
                                String insertValue = "";
                                updateSql = "update " + tableName
                                        + " set LastModifiedTime=?,LastOperatorID=?,Status=1";
                                String updateValue = "";
                                Object[] updateObject = new Object[importTemplateDefList
                                        .size() + 2];
                                updateObject[0] = df.format(new Date());
                                updateObject[1] = createId;
                                // 判断导入表中是否已经存在数据
                                int tablePkIndex = letterIntoDigitalByOne(importTemplateDefList
                                        .get(0).getPkExcelColumnName()) - 1;
                                String tablePkValue = (String) sheetMap.get(String
                                        .valueOf(tablePkIndex));
                                sql = "select * from " + tableName
                                        + " where DeviceName='" + tablePkValue
                                        + "' and CompanyId=" + companyId;
                                List<Map<String, Object>> pKMapList = jdbcTemplate
                                        .queryForList(sql);
                                _logger.info("pKMapList.size========" + pKMapList.size());
                                if (pKMapList.size() > 0) {
                                    for (int j = 0; j < importTemplateDefList
                                            .size(); j++) {
                                        // excelCellValue表中EXCEL对应值
                                        String excelCellValue = "";
                                        int excelCellIndex = -1;
                                        if (importTemplateDefList.get(j)
                                                .getExcelColumnName().length() == 1) {
                                            excelCellIndex = letterIntoDigitalByOne(importTemplateDefList
                                                    .get(j).getExcelColumnName()) - 1;
                                        } else {
                                            excelCellIndex = letterIntoDigitalByTwo(importTemplateDefList
                                                    .get(j).getExcelColumnName()) - 1;
                                        }
                                        excelCellValue = (String) sheetMap
                                                .get(String.valueOf(excelCellIndex));
                                        // 判断字段转换SQL
                                        String translateSql = importTemplateDefList
                                                .get(j).getTranslateSql();
                                        if (translateSql != null
                                                && !"".equals(translateSql)
                                                && !"null".equals(translateSql)) {
                                            Map mapSqlParm = new HashMap();
//            								mapSqlParm.put("DeviceName",
//            										excelCellValue);
//            								mapSqlParm.put("CompanyId", companyId);
                                            if (translateSql.contains(":DeviceName")) {
                                                mapSqlParm.put("DeviceName",
                                                        excelCellValue);
                                            }
                                            if (translateSql.contains(":DEVICE_TYPE_NAME")) {
                                                mapSqlParm.put("DEVICE_TYPE_NAME",
                                                        excelCellValue);
                                            }

                                            if (translateSql.contains(":CompanyId")) {
                                                mapSqlParm.put("CompanyId",
                                                        companyId);
                                            }
                                            try {
                                                List<Map<String, Object>> translateMapList = simpleJdbcTemplate
                                                        .queryForList(translateSql,
                                                                mapSqlParm);
                                                if (translateMapList.size() > 0) {
                                                    Map map = translateMapList.get(0);
                                                    for (Iterator it = map.keySet()
                                                            .iterator(); it.hasNext(); ) {
                                                        String key = (String) it.next();
                                                        excelCellValue = map
                                                                .get(key) + "";
                                                    }
                                                } else {
                                                    excelCellValue = "";
                                                }
                                            } catch (Exception e) {
                                                e.printStackTrace();
                                                returnMap.put("success", successNum);
                                                return returnMap;
                                            }
                                        }
                                        // 拼接update sql
                                        updateValue += ", "
                                                + importTemplateDefList.get(j)
                                                .getTableFieldName() + "=?";
                                        if (excelCellValue != null && excelCellValue.trim().equals("")) {
                                            updateObject[j + 2] = null;
                                        } else {
                                            updateObject[j + 2] = excelCellValue;
                                        }
                                    }
                                    updateSql = updateSql + updateValue
                                            + " where DeviceName='" + tablePkValue
                                            + "'";
                                    try {
                                        returnCode = jdbcTemplate.update(updateSql, updateObject);
                                    } catch (Exception e) {
                                        e.printStackTrace();
                                    }
                                    if (returnCode >= 1) {
                                        successNum++;
                                        updateNum++;
                                    } else {
                                        fuilureLineNum.add(sheetName + ":第"
                                                + (k + 1) + "行");
                                    }
                                } else {
                                    for (int j = 0; j < importTemplateDefList
                                            .size(); j++) {
                                        // excelCellValue表中EXCEL对应值
                                        String excelCellValue = "";
                                        int excelCellIndex = -1;
                                        _logger.info("importTemplateDefList[" + j + "]getExcelColumnName=====" + importTemplateDefList.get(j).getExcelColumnName());
                                        if (importTemplateDefList.get(j)
                                                .getExcelColumnName().length() == 1) {
                                            excelCellIndex = letterIntoDigitalByOne(importTemplateDefList
                                                    .get(j).getExcelColumnName()) - 1;
                                        } else {
                                            excelCellIndex = letterIntoDigitalByTwo(importTemplateDefList
                                                    .get(j).getExcelColumnName()) - 1;
                                        }
                                        _logger.info("excelCellIndex========" + excelCellIndex);
                                        excelCellValue = (String) sheetMap
                                                .get(String.valueOf(excelCellIndex));
                                        // 判断字段转换SQL
                                        String translateSql = importTemplateDefList
                                                .get(j).getTranslateSql();
                                        _logger.info("importTemplateDefList[" + j + "]getTranslateSql=====" + importTemplateDefList.get(j).getTranslateSql());
                                        if (translateSql != null
                                                && !"".equals(translateSql)
                                                && !"null".equals(translateSql)) {
                                            Map mapSqlParm = new HashMap();
//            								mapSqlParm.put("DeviceName",
//            										excelCellValue);
//            								mapSqlParm.put("CompanyId", companyId);
                                            if (translateSql.contains(":DeviceName")) {
                                                mapSqlParm.put("DeviceName",
                                                        excelCellValue);
                                            }
                                            if (translateSql.contains(":DEVICE_TYPE_NAME")) {
                                                mapSqlParm.put("DEVICE_TYPE_NAME",
                                                        excelCellValue);
                                            }

                                            if (translateSql.contains(":CompanyId")) {
                                                mapSqlParm.put("CompanyId",
                                                        companyId);
                                            }
                                            try {
                                                List<Map<String, Object>> translateMapList = simpleJdbcTemplate
                                                        .queryForList(translateSql,
                                                                mapSqlParm);
                                                _logger.info("translateMapList.size=====" + translateMapList.size());
                                                if (translateMapList.size() > 0) {
                                                    Map map = translateMapList.get(0);
                                                    for (Iterator it = map.keySet()
                                                            .iterator(); it.hasNext(); ) {
                                                        String key = (String) it.next();
                                                        excelCellValue = map
                                                                .get(key) + "";
                                                    }
                                                } else {
                                                    excelCellValue = "";
                                                }
                                            } catch (Exception e) {
                                                e.printStackTrace();
                                                returnMap.put("success", successNum);
                                                return returnMap;
                                            }
                                        }
                                        // 拼接insert sql
                                        _logger.info("importTemplateDefList[" + j + "]getTableFieldName=====" + importTemplateDefList.get(j).getTableFieldName());
                                        if (j == 0) {
                                            insertColumn += ","
                                                    + importTemplateDefList.get(j)
                                                    .getTableFieldName();
                                            insertValue += " values(?";
                                        } else if ((j + 1) == importTemplateDefList
                                                .size()) {
                                            insertColumn += ","
                                                    + importTemplateDefList.get(j)
                                                    .getTableFieldName()
                                                    + ")";
                                            insertValue += ",?,?,?,?,?,?,?,?)";
                                        } else {
                                            insertColumn += ","
                                                    + importTemplateDefList.get(j)
                                                    .getTableFieldName();
                                            insertValue += ",?";
                                        }
                                        if (excelCellValue != null && excelCellValue.trim().equals("")) {
                                            insertObject[j + 7] = null;
                                        } else {
                                            insertObject[j + 7] = excelCellValue;
                                        }
                                        //insertObject[j + 7] = excelCellValue.trim().equals("")?null:excelCellValue;
                                        _logger.info("excelCellValue=====" + excelCellValue);
                                    }
                                    insertSql = insertSql + insertColumn
                                            + insertValue;
                                    try {
                                        returnCode = jdbcTemplate.update(insertSql, insertObject);
                                    } catch (Exception e) {
                                        e.printStackTrace();
                                    }
                                    if (returnCode == 1) {
                                        successNum++;
                                        insertNum++;
                                    } else {
                                        fuilureLineNum.add(sheetName + ":第"
                                                + (k + 1) + "行");
                                    }
                                }
                            } else {
                                _logger
                                        .info("importExcle:importTemplateDefList.size() =0");
                                returnMap.put("success", successNum);
                                return returnMap;
                            }
                        }
                    } else {
                        _logger.info("importExcle:sheetMapList.size() =0");
                        returnMap.put("success", successNum);
                        return returnMap;
                    }
                }
            } else {
                _logger.info("importExcle:mapList.size() =0");
            }
            returnMap.put("success", successNum);
            returnMap.put("insert", insertNum);
            returnMap.put("update", updateNum);
            returnMap.put("failure", fuilureLineNum);
            _logger.info("fuilureLineNum szie========" + fuilureLineNum.size());
        } catch (DataAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return returnMap;
    }

    @Override
    public String exportVzdyUserList(List<Vzdy> list, long companyId) throws PwtzDatabaseException {
        // 返回成功失败表示:2为失败
        String returnCode = "2";

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("人员进度统计");
        if (list.size() > 0) {
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);
            sheet.autoSizeColumn(0, true);
            cell.setCellValue("单位名称");
            cell = row.createCell(1);
            sheet.autoSizeColumn(1, true);
            cell.setCellValue("人员");
            cell = row.createCell(2);
            sheet.autoSizeColumn(2, true);
            cell.setCellValue("电压等级");
            cell = row.createCell(3);
            sheet.autoSizeColumn(3, true);
            cell.setCellValue("设备类型");
            cell = row.createCell(4);
            sheet.autoSizeColumn(4, true);
            cell.setCellValue("数量");
            int count = 0;
            for (int i = 0; i < list.size(); i++) {
                row = sheet.createRow(i + 1);
                cell = row.createCell(0);
                sheet.autoSizeColumn(0, true);
                cell.setCellValue(list.get(i).getCompanys().getCompanyName());
                cell = row.createCell(1);
                sheet.autoSizeColumn(1, true);
                if (list.get(i).getOperators() != null) {
                    cell.setCellValue(list.get(i).getOperators().getLoginName());
                } else {
                    cell.setCellValue("");
                }
                cell = row.createCell(2);
                sheet.autoSizeColumn(2, true);
                cell.setCellValue(list.get(i).getLevename());
                cell = row.createCell(3);
                sheet.autoSizeColumn(3, true);
                cell.setCellValue(list.get(i).getDevicetypename());
                cell = row.createCell(4);
                sheet.autoSizeColumn(4, true);
                cell.setCellValue(list.get(i).getNumber());

                count += list.get(i).getNumber();
            }
            row = sheet.createRow(list.size() + 1);
            cell = row.createCell(0);
            sheet.autoSizeColumn(0, true);
            cell.setCellValue("合计");
            cell = row.createCell(4);
            sheet.autoSizeColumn(4, true);
            cell.setCellValue(count);
        }

        // 读取输出文件路径并输出excle
        String dir = "";
        String fileName = "";
        List<Config> exportVzdyPathList = configMapper.selectByKey("ExportVzdyPath");
        if (exportVzdyPathList != null && exportVzdyPathList.size() > 0) {
            dir = exportVzdyPathList.get(0).getConfigValue();
            dir = dir.replace("@COMPANYID", String.valueOf(companyId));
            dir = dir.replace("@TIMESTAMP", String.valueOf(System.currentTimeMillis()));
        }
        // 临时存放图片文件夹
        //dir = "D:\\" + System.currentTimeMillis();
        File fdir = new File(dir);
        if (!fdir.exists()) {
            fdir.mkdirs();
        }
        List<Config> exportExcelNameList = configMapper.selectByKey("ExportVzdyUserListName");
        if (exportExcelNameList != null
                && exportExcelNameList.size() > 0) {
            fileName = exportExcelNameList.get(0).getConfigValue();
        }
        _logger.info("dir=" + dir);
        _logger.info("fileName=" + fileName);
        FileOutputStream os = null;
        try {
            //os = new FileOutputStream(dir + "\\testvzdy.xls");
            os = new FileOutputStream(dir + fileName);
            wb.write(os);
            os.close();
            returnCode = dir + fileName;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        _logger.info("returnCode =======" + returnCode);
        return returnCode;
    }

    @Override
    public String exportVzdyList(List<Vzdy> list, long companyId) throws PwtzDatabaseException {
        // 返回成功失败表示:2为失败
        String returnCode = "2";

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("设备类型数据统计");
        if (list.size() > 0) {
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);
            sheet.autoSizeColumn(0, true);
            cell.setCellValue("单位名称");
            cell = row.createCell(1);
            sheet.autoSizeColumn(1, true);
            cell.setCellValue("电压等级");
            cell = row.createCell(2);
            sheet.autoSizeColumn(2, true);
            cell.setCellValue("设备类型");
            cell = row.createCell(3);
            sheet.autoSizeColumn(3, true);
            cell.setCellValue("数量");
            int count = 0;
            for (int i = 0; i < list.size(); i++) {
                row = sheet.createRow(i + 1);
                cell = row.createCell(0);
                sheet.autoSizeColumn(0, true);
                cell.setCellValue(list.get(i).getCompanys().getCompanyName());
                cell = row.createCell(1);
                sheet.autoSizeColumn(1, true);
                cell.setCellValue(list.get(i).getLevename());
                cell = row.createCell(2);
                sheet.autoSizeColumn(2, true);
                cell.setCellValue(list.get(i).getDevicetypename());
                cell = row.createCell(3);
                sheet.autoSizeColumn(3, true);
                cell.setCellValue(list.get(i).getNumber());

                count += list.get(i).getNumber();
            }
            row = sheet.createRow(list.size() + 1);
            cell = row.createCell(0);
            sheet.autoSizeColumn(0, true);
            cell.setCellValue("合计");
            cell = row.createCell(3);
            sheet.autoSizeColumn(3, true);
            cell.setCellValue(count);
        }

        // 读取输出文件路径并输出excle
        String dir = "";
        String fileName = "";
        List<Config> exportVzdyPathList = configMapper.selectByKey("ExportVzdyPath");
        if (exportVzdyPathList != null && exportVzdyPathList.size() > 0) {
            dir = exportVzdyPathList.get(0).getConfigValue();
            dir = dir.replace("@COMPANYID", String.valueOf(companyId));
            dir = dir.replace("@TIMESTAMP", String.valueOf(System.currentTimeMillis()));
        }
        // 临时存放图片文件夹
        //dir = "D:\\" + System.currentTimeMillis();
        File fdir = new File(dir);
        if (!fdir.exists()) {
            fdir.mkdirs();
        }
        List<Config> exportExcelNameList = configMapper.selectByKey("ExportVzdyListName");
        if (exportExcelNameList != null
                && exportExcelNameList.size() > 0) {
            fileName = exportExcelNameList.get(0).getConfigValue();
        }
        _logger.info("dir=" + dir);
        _logger.info("fileName=" + fileName);
        FileOutputStream os = null;
        try {
            //os = new FileOutputStream(dir + "\\testvzdy.xls");
            os = new FileOutputStream(dir + fileName);
            wb.write(os);
            os.close();
            returnCode = dir + fileName;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        _logger.info("returnCode =======" + returnCode);
        return returnCode;
    }


    @Override
    public String exportVdystatList(List<Vdystat> list, String companyName, String path, boolean bl) throws PwtzDatabaseException {
        // 返回成功失败表示:2为失败
        String returnCode = "2";

        String excelPath = PwtzConstants.VDYSTAT_TEMPLATE;
        //excelPath="D:\\temp\\vdystat.xlsx";
        File excelFile = new File(excelPath);
        if (!excelFile.exists()) {
            return returnCode;
        }
        XSSFWorkbook wb = new XSSFWorkbook();
        try {
            FileInputStream in = new FileInputStream(excelFile);
            wb = new XSSFWorkbook(in);
        } catch (FileNotFoundException e) {
            return returnCode;
        } catch (IOException e) {
            return returnCode;
        }
        XSSFSheet sheet = wb.getSheetAt(0);
        if (list.size() > 0) {
            XSSFRow row = sheet.getRow(2);
            XSSFCell cell = null;
            if (!"".equals(companyName)) {
                cell = row.getCell(0);
                sheet.autoSizeColumn(0, true);
                cell.setCellValue("填报单位：" + companyName);
                cell = row.getCell(4);
                sheet.autoSizeColumn(4, true);
                cell.setCellValue("填报日期：" + PwtzUtils.date2String(new Date()));
            }
            int alldyxlcount = 0;
            int alldyxltotallinelength = 0;
            int alldyxloverheadlinelength = 0;
            int alldyxloverheadlinelengthBar = 0;
            int alldyxloverheadlinelengthCover = 0;
            int alldyxlcablelength = 0;
            int alldygtcount = 0;
            int alllxcount = 0;
            int allpublicbyqcount = 0;
            int allpublicbyqcapacity = 0;
            int allprivatebyqcount = 0;
            int allprivatebyqcapacity = 0;
            int alldypdxcount = 0;
            int alldydlfjxcount = 0;
            int alldyzsrdqcount = 0;
            int alldyzskgcount = 0;
            int alldywgbczzcount = 0;
            int alldyzsblqcount = 0;
            int alldyrsxcount = 0;
            int alldyzsdrqcount = 0;
            int alldyzsdrqcapacity = 0;
            for (int i = 0; i < list.size(); i++) {
                row = sheet.getRow(i + 7);
                int count = 0;
                cell = row.getCell(count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(i + 1);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getCompanyName());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDyxlcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDyxltotallinelength());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDyxloverheadlinelength());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDyxloverheadlinelengthBar());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDyxloverheadlinelengthCover());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDyxlcablelength());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDygtcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getLxcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getPublicbyqcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getPublicbyqcapacity());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getPrivatebyqcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getPrivatebyqcapacity());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDypdxcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDydlfjxcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDyzsrdqcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDyzskgcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDywgbczzcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDyzsblqcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDyrsxcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDyzsdrqcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getDyzsdrqcapacity());
                if (bl) {
                    alldyxlcount += list.get(i).getDyxlcount();
                    alldyxltotallinelength += list.get(i).getDyxltotallinelength();
                    alldyxloverheadlinelength += list.get(i).getDyxloverheadlinelength();
                    alldyxloverheadlinelengthBar += list.get(i).getDyxloverheadlinelengthBar();
                    alldyxloverheadlinelengthCover += list.get(i).getDyxloverheadlinelengthCover();
                    alldyxlcablelength += list.get(i).getDyxlcablelength();
                    alldygtcount += list.get(i).getDygtcount();
                    alllxcount += list.get(i).getLxcount();
                    allpublicbyqcount += list.get(i).getPublicbyqcount();
                    allpublicbyqcapacity += list.get(i).getPublicbyqcapacity();
                    allprivatebyqcount += list.get(i).getPrivatebyqcount();
                    allprivatebyqcapacity += list.get(i).getPrivatebyqcapacity();
                    alldypdxcount += list.get(i).getDypdxcount();
                    alldydlfjxcount += list.get(i).getDydlfjxcount();
                    alldyzsrdqcount += list.get(i).getDyzsrdqcount();
                    alldyzskgcount += list.get(i).getDyzskgcount();
                    alldywgbczzcount += list.get(i).getDywgbczzcount();
                    alldyzsblqcount += list.get(i).getDyzsblqcount();
                    alldyrsxcount += list.get(i).getDyrsxcount();
                    alldyzsdrqcount += list.get(i).getDyzsdrqcount();
                    alldyzsdrqcapacity += list.get(i).getDyzsdrqcapacity();
                }
            }
            if (bl) {
                row = sheet.getRow(list.size() + 7);
                cell = row.getCell(0);
                sheet.autoSizeColumn(0, true);
                cell.setCellValue("合计");
                int count = 1;
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldyxlcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldyxltotallinelength);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldyxloverheadlinelength);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldyxloverheadlinelengthBar);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldyxloverheadlinelengthCover);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldyxlcablelength);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldygtcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alllxcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allpublicbyqcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allpublicbyqcapacity);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allprivatebyqcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allprivatebyqcapacity);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldypdxcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldydlfjxcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldyzsrdqcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldyzskgcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldywgbczzcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldyzsblqcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldyrsxcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldyzsdrqcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alldyzsdrqcapacity);
            }

        }

        // 读取输出文件路径并输出excle
        String dir = "";
        String fileName = "";
        String prefix = "";
        String newFileName = "";
        fileName = excelFile.getPath();
        prefix = fileName.substring(fileName.lastIndexOf(".")).toLowerCase();
        newFileName = System.currentTimeMillis() + PwtzUtils.getRandomNumber(4) + prefix;
        dir = path + newFileName;

        FileOutputStream os = null;
        try {
            File pathfile = new File(path);
            if (!pathfile.exists()) {
                pathfile.mkdirs();
            }
            os = new FileOutputStream(dir);
            wb.write(os);
            os.close();
            returnCode = dir;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        _logger.info("returnCode =======" + returnCode);
        return returnCode;
    }


    @Override
    public String exportVzystatList(List<Vzystat> list, String companyName, String path, boolean bl) throws PwtzDatabaseException {
        // 返回成功失败表示:2为失败
        String returnCode = "2";

        String excelPath = PwtzConstants.VZYSTAT_TEMPLATE;
        //excelPath="D:\\temp\\vzystat.xlsx";
        File excelFile = new File(excelPath);
        if (!excelFile.exists()) {
            return returnCode;
        }
        XSSFWorkbook wb = new XSSFWorkbook();
        try {
            FileInputStream in = new FileInputStream(excelFile);
            wb = new XSSFWorkbook(in);
        } catch (FileNotFoundException e) {
            return returnCode;
        } catch (IOException e) {
            return returnCode;
        }
        XSSFSheet sheet = wb.getSheetAt(0);
        if (list.size() > 0) {
            XSSFRow row = sheet.getRow(2);
            XSSFCell cell = null;
            if (!"".equals(companyName)) {
                cell = row.getCell(0);
                sheet.autoSizeColumn(0, true);
                cell.setCellValue("填报单位：" + companyName);
                cell = row.getCell(5);
                sheet.autoSizeColumn(5, true);
                cell.setCellValue("填报日期：" + PwtzUtils.date2String(new Date()));
            }
            int allzyxlmainlinecount = 0;
            int allzyxlsublinecount = 0;
            int allzyxltotallinelength = 0;
            int allzyxloverheadlinelength = 0;
            int allzyxloverheadlinelengthBar = 0;
            int allzyxloverheadlinelengthCover = 0;
            int allzyxlcablelength = 0;
            int allzygtcount = 0;
            int alllxcount = 0;
            int allpublicbyqcount = 0;
            int allpublicbyqcapacity = 0;
            int allprivatebyqcount = 0;
            int allprivatebyqcapacity = 0;
            int allzykgzcount = 0;
            int allzypdscount = 0;
            int allzyhwgcount = 0;
            int allzyxsbdzcount = 0;
            int allzydlfzxcount = 0;
            int allzyzsdlqcount = 0;
            int allzyzsfhkgcount = 0;
            int allzyzsglkgcount = 0;
            int allzyzschqcount = 0;
            int allzyzsdlsrdqcount = 0;
            int allzyzsblqcount = 0;
            int allzyzsdrqcount = 0;
            int allzyzsdrqcapacity = 0;
            for (int i = 0; i < list.size(); i++) {
                row = sheet.getRow(i + 7);
                int count = 0;
                cell = row.getCell(count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(i + 1);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getCompanyName());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyxlmainlinecount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyxlsublinecount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyxltotallinelength());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyxloverheadlinelength());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyxloverheadlinelengthBar());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyxloverheadlinelengthCover());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyxlcablelength());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZygtcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getLxcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getPublicbyqcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getPublicbyqcapacity());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getPrivatebyqcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getPrivatebyqcapacity());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZykgzcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZypdscount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyhwgcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyxsbdzcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZydlfzxcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyzsdlqcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyzsfhkgcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyzsglkgcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyzschqcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyzsdlsrdqcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyzsblqcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyzsdrqcount());
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(list.get(i).getZyzsdrqcapacity());
                if (bl) {
                    allzyxlmainlinecount += list.get(i).getZyxlmainlinecount();
                    allzyxlsublinecount += list.get(i).getZyxlsublinecount();
                    allzyxltotallinelength += list.get(i).getZyxltotallinelength();
                    allzyxloverheadlinelength += list.get(i).getZyxloverheadlinelength();
                    allzyxloverheadlinelengthBar += list.get(i).getZyxloverheadlinelengthBar();
                    allzyxloverheadlinelengthCover += list.get(i).getZyxloverheadlinelengthCover();
                    allzyxlcablelength += list.get(i).getZyxlcablelength();
                    allzygtcount += list.get(i).getZygtcount();
                    alllxcount += list.get(i).getLxcount();
                    allpublicbyqcount += list.get(i).getPublicbyqcount();
                    allpublicbyqcapacity += list.get(i).getPublicbyqcapacity();
                    allprivatebyqcount += list.get(i).getPrivatebyqcount();
                    allprivatebyqcapacity += list.get(i).getPrivatebyqcapacity();
                    allzykgzcount += list.get(i).getZykgzcount();
                    allzypdscount += list.get(i).getZypdscount();
                    allzyhwgcount += list.get(i).getZyhwgcount();
                    allzyxsbdzcount += list.get(i).getZyxsbdzcount();
                    allzydlfzxcount += list.get(i).getZydlfzxcount();
                    allzyzsdlqcount += list.get(i).getZyzsdlqcount();
                    allzyzsfhkgcount += list.get(i).getZyzsfhkgcount();
                    allzyzsglkgcount += list.get(i).getZyzsglkgcount();
                    allzyzschqcount += list.get(i).getZyzschqcount();
                    allzyzsdlsrdqcount += list.get(i).getZyzsdlsrdqcount();
                    allzyzsblqcount += list.get(i).getZyzsblqcount();
                    allzyzsdrqcount += list.get(i).getZyzsdrqcount();
                    allzyzsdrqcapacity += list.get(i).getZyzsdrqcapacity();
                }
                //count += list.get(i).getNumber();
            }
            if (bl) {
                row = sheet.getRow(list.size() + 7);
                cell = row.getCell(0);
                sheet.autoSizeColumn(0, true);
                cell.setCellValue("合计");
                int count = 1;
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyxlmainlinecount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyxlsublinecount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyxltotallinelength);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyxloverheadlinelength);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyxloverheadlinelengthBar);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyxloverheadlinelengthCover);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyxlcablelength);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzygtcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(alllxcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allpublicbyqcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allpublicbyqcapacity);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allprivatebyqcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allprivatebyqcapacity);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzykgzcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzypdscount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyhwgcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyxsbdzcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzydlfzxcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyzsdlqcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyzsfhkgcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyzsglkgcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyzschqcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyzsdlsrdqcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyzsblqcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyzsdrqcount);
                cell = row.getCell(++count);
                sheet.autoSizeColumn(count, true);
                cell.setCellValue(allzyzsdrqcapacity);

            }

        }

        // 读取输出文件路径并输出excle
        String dir = "";
        String fileName = "";
        String prefix = "";
        String newFileName = "";
        fileName = excelFile.getPath();
        prefix = fileName.substring(fileName.lastIndexOf(".")).toLowerCase();
        newFileName = System.currentTimeMillis() + PwtzUtils.getRandomNumber(4) + prefix;
        dir = path + newFileName;

        FileOutputStream os = null;
        try {
            File pathfile = new File(path);
            if (!pathfile.exists()) {
                pathfile.mkdirs();
            }
            os = new FileOutputStream(dir);
            wb.write(os);
            os.close();
            returnCode = dir;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        _logger.info("returnCode =======" + returnCode);
        return returnCode;
    }

    public String DYPMSExel(long companyId, long templateId, String LineCode, String deviceName) {
        // 返回成功失败表示:2为失败
        String returnCode = "2";
        try {
            Map<String,String> UUIDMAP=new HashMap<>();
            ExportTemplate exportTemplate = exportTemplateMapper
                    .selectTemplateByIdAndStatus(templateId, 0);
            if (exportTemplate != null) {
                _logger.info("exportTemplate.getExcelPath()="
                        + exportTemplate.getExcelPath());
                String excelPath = exportTemplate.getExcelPath();
                File excelFile = new File(excelPath);
                if (!excelFile.exists()) {
                    return returnCode;
                }
                HSSFWorkbook wb = new HSSFWorkbook();
                try {
                    wb = new HSSFWorkbook(new FileInputStream(excelFile));
                } catch (FileNotFoundException e) {
                    return returnCode;
                } catch (IOException e) {
                    return returnCode;
                }
                List<ExportTemplateDef> exportTemplateDefList = exportTemplateDefMapper
                        .selectTemplateDefByTemplateId(templateId);
                _logger.info("exportTemplateDefList size ="
                        + exportTemplateDefList.size());
                if (exportTemplateDefList.size() > 0) {
                    for (int i = 0; i < exportTemplateDefList.size(); i++) {
                        ExportTemplateDef exportTemplateDef = new ExportTemplateDef();
                        exportTemplateDef = exportTemplateDefList.get(i);
                        // 通过EXECUTE_SQL生成对应每个sheet提取的表数据
                        String sheetSql = exportTemplateDef.getExecuteSql();
                        Map mapSqlParm = new HashMap();
                        mapSqlParm.put("LineCode", LineCode);
                        List<Map<String, Object>> mapList = simpleJdbcTemplate
                                .queryForList(sheetSql, LineCode);
                        _logger.info("mapList size =" + mapList.size() + "LineCode:" + LineCode);
                        // 获取对应sheet
                        _logger.info("exportTemplateDef.getSheetName() ="
                                + exportTemplateDef.getSheetName());
                        HSSFSheet sheet = wb.getSheet(exportTemplateDef
                                .getSheetName());
                        // 获取赋值行数
                        int rowIndex = exportTemplateDef.getStartRowIndex();
                        // 通过mapList进行excle赋值
                        if (mapList.size() > 0) {
                            for (int j = 0; j < mapList.size(); j++) {
                                HSSFRow row = sheet.createRow(rowIndex);
                                Map map = mapList.get(j);
                                HSSFCell cell = row.createCell(0);
                                sheet.autoSizeColumn(0, true);
                                cell.setCellValue(j + 1);
                                int k = 1;
                                for (Iterator it = map.keySet().iterator(); it
                                        .hasNext(); ) {
                                    String key = (String) it.next();
                                    cell = row.createCell(k);
                                    sheet.autoSizeColumn(k, true);
                                    if (exportTemplateDef.getSheetName().equals("11.用户接入点模板") && key.equals("yhjhxid")) {
                                        String value = UUID.randomUUID().toString();
                                        cell.setCellValue(value);
                                        UUIDMAP.put(map.get("DeviceID").toString(), value);
                                    } else if (exportTemplateDef.getSheetName().equals("11.用户接入点模板") && key.equals("Longitude")) {
                                        Double longitude=Double.parseDouble(map.get("Longitude").toString())+0.002;
                                        cell.setCellValue(longitude);
                                    } else if(exportTemplateDef.getSheetName().equals("11.用户接入点模板") && key.equals("DeviceID")){
                                        continue;
                                    }else if(exportTemplateDef.getSheetName().equals("12.接户线") && key.equals("UUID")){
                                        String value = UUIDMAP.get(map.get("DeviceID"));
                                        cell.setCellValue(value);
                                    }else if(exportTemplateDef.getSheetName().equals("12.接户线") && key.equals("DeviceID")){
                                        continue;
                                    }
                                    else {
                                        cell.setCellValue((map.get(key) == null || map.get(key).equals("null") || map.get(key).equals("NULL")) ? "" : map.get(key) + "");
                                    }
                                    k++;
                                }
                                rowIndex++;
                            }
                        } else {
                            _logger.info("jdbcTemplate.queryForList(" + sheetSql
                                    + "):mapList.size() =0");
                            if ((i + 1) == mapList.size()) {
                                return returnCode;
                            }
                        }
                    }
                    // 读取输出文件路径并输出excle
                    String dir = "";
                    String fileName = deviceName + ".xls";
                    List<Config> exportDataPathList = configMapper
                            .selectByKey("PMSExportDYPath");
                    if (exportDataPathList != null && exportDataPathList.size() > 0) {
                        dir = exportDataPathList.get(0).getConfigValue();
                        dir = dir.replace("@COMPANYID", String.valueOf(companyId));
                        dir = dir.replace("@TIMESTAMP", String.valueOf(System
                                .currentTimeMillis()));
                    }
                    File fdir = new File(dir);
                    if (!fdir.exists()) {
                        fdir.mkdirs();
                    }
//					List<Config> exportExcelNameList = configMapper
//							.selectByKey("PMSExportDYName");
//					if (exportExcelNameList != null
//							&& exportExcelNameList.size() > 0) {
//						fileName = exportExcelNameList.get(0).getConfigValue();
//					}
                    _logger.info("dir=" + dir);
                    _logger.info("fileName=" + fileName);
                    FileOutputStream os = null;
                    try {
                        //os = new FileOutputStream(dir + "\\test1.xls");
                        os = new FileOutputStream(dir + fileName);
                        wb.write(os);
                        os.close();
                        returnCode = dir + fileName;
                    } catch (FileNotFoundException e) {
                        e.printStackTrace();
                    } catch (IOException e) {
                        e.printStackTrace();
                    } finally {
                        try {
                            os.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                } else {
                    _logger
                            .info("exportTemplateDefMapper.selectTemplateDefByTemplateId("
                                    + templateId
                                    + "):exportTemplateDefList.size() =0");
                }
            } else {
                _logger.info("exportTemplateMapper.selectTemplateByIdAndStatus("
                        + templateId + ",1):is null");
            }
            _logger.info("returnCode =======" + returnCode);
        } catch (Exception e) {
            _logger.info(e);
            e.printStackTrace();
        }
        return returnCode;
    }

    public String DYAllPMSExel(long companyId, long templateId, String LineCode, String deviceName, int deviceType, String transformerCode) {
        // 返回成功失败表示:2为失败
        String returnCode = "2";
        try {
            ExportTemplate exportTemplate = exportTemplateMapper
                    .selectTemplateByIdAndStatus(templateId, 0);
            Map<String, String> UUIDMAP = new HashMap<>();//用于存储用户接户点UUID
            if (exportTemplate != null) {
                _logger.info("exportTemplate.getExcelPath()="
                        + exportTemplate.getExcelPath());
                String excelPath = exportTemplate.getExcelPath();
                File excelFile = new File(excelPath);
                if (!excelFile.exists()) {
                    return returnCode;
                }
                HSSFWorkbook wb = new HSSFWorkbook();
                try {
                    wb = new HSSFWorkbook(new FileInputStream(excelFile));
                } catch (FileNotFoundException e) {
                    return returnCode;
                } catch (IOException e) {
                    return returnCode;
                }
                List<ExportTemplateDef> exportTemplateDefList = exportTemplateDefMapper
                        .selectTemplateDefByTemplateId(templateId);
                _logger.info("exportTemplateDefList size ="
                        + exportTemplateDefList.size());
                List<Dyxl> list = null;
                if (deviceType == 1) {
                    list = dyxlService.selectDyxlAllBypdbyq(deviceType, LineCode, "", companyId);
                } else {
                    list = dyxlService.selectDyxlAllBypdbyq(deviceType, LineCode, "", companyId);
                }
                if (exportTemplateDefList.size() > 0) {
                    for (int i = 0; i < exportTemplateDefList.size(); i++) {
                        ExportTemplateDef exportTemplateDef = new ExportTemplateDef();
                        exportTemplateDef = exportTemplateDefList.get(i);
                        // 通过EXECUTE_SQL生成对应每个sheet提取的表数据
                        String sheetSql = exportTemplateDef.getExecuteSql();
                        //查询变电器下的线路

                        List<Map<String, Object>> mapList = new ArrayList<>();

                        for (Dyxl dyxl : list) {
                            _logger.info("LineCode:" + dyxl.getDeviceid());
                            List<Map<String, Object>> mapList1 = simpleJdbcTemplate
                                    .queryForList(sheetSql, dyxl.getDeviceid());
                            mapList.addAll(mapList1);
                        }

//						Map mapSqlParm = new HashMap();
//						mapSqlParm.put("LineCode", LineCode);
//						List<Map<String, Object>> mapList = simpleJdbcTemplate
//								.queryForList(sheetSql, LineCode);
                        _logger.info("mapList size =" + mapList.size());
                        // 获取对应sheet
                        _logger.info("exportTemplateDef.getSheetName() ="
                                + exportTemplateDef.getSheetName());
                        HSSFSheet sheet = wb.getSheet(exportTemplateDef
                                .getSheetName());
                        // 获取赋值行数
                        int rowIndex = exportTemplateDef.getStartRowIndex();
                        // 通过mapList进行excle赋值
                        if (mapList.size() > 0) {
                            for (int j = 0; j < mapList.size(); j++) {
                                HSSFRow row = sheet.createRow(rowIndex);
                                Map map = mapList.get(j);
                                HSSFCell cell = row.createCell(0);
                                sheet.autoSizeColumn(0, true);
                                cell.setCellValue(j + 1);
                                int k = 1;
                                int o=1;
                                for (Iterator it = map.keySet().iterator(); it.hasNext(); ) {
                                    String key = (String) it.next();
                                    cell = row.createCell(k);
                                    sheet.autoSizeColumn(k, true);
                                    if (exportTemplateDef.getSheetName().equals("11.用户接入点模板") && key.equals("yhjhxid")) {
                                        String value = UUID.randomUUID().toString();
                                        cell.setCellValue(value);
                                        UUIDMAP.put(map.get("DeviceID").toString(), value);
                                    } else if (exportTemplateDef.getSheetName().equals("11.用户接入点模板") && key.equals("Longitude")) {
                                        if(!StringUtils.isEmpty(map.get("Longitude").toString())){
                                            Double longitude=Double.parseDouble(map.get("Longitude").toString())+0.00002;
                                            cell.setCellValue(longitude);
                                        }else{
                                            cell.setCellValue("");
                                        }
                                    }else if(exportTemplateDef.getSheetName().equals("11.用户接入点模板") && key.equals("DeviceName")){
                                        String value=map.get(key).toString();
                                        if(value.contains("#杆")){
                                            value=value.substring(value.indexOf("#杆")+2);
                                        }
                                        if(value.contains("表箱")){
                                            value=value.substring(value.indexOf("表箱")+2);
                                        }
                                        if(value.contains("#")){
                                            value=value.substring(value.lastIndexOf("#")+1);
                                        }
                                        cell.setCellValue(value);
                                    }
                                    else if(exportTemplateDef.getSheetName().equals("11.用户接入点模板") && key.equals("DeviceID")){
                                        continue;
                                    }
                                    else if(exportTemplateDef.getSheetName().equals("12.接户线") && key.equals("UUID")){
                                        String value = UUIDMAP.get(map.get("DeviceID"));
                                        cell.setCellValue(value);
                                    }else if(exportTemplateDef.getSheetName().equals("12.接户线") && key.equals("DeviceID")){
                                        continue;
                                    }
                                    else {
                                        cell.setCellValue((map.get(key) == null || map.get(key).equals("null") || map.get(key).equals("NULL")) ? "" : map.get(key) + "");
                                    }
                                    k++;
                                }
                                rowIndex++;
                            }
                        } else {
                            _logger.info("jdbcTemplate.queryForList(" + sheetSql
                                    + "):mapList.size() =0");
                            if ((i + 1) == mapList.size()) {
                                return returnCode;
                            }
                        }
                    }
                    // 读取输出文件路径并输出excle
                    String dir = "";

                    String fileName = deviceName + ".xls";
                    List<Config> exportDataPathList = configMapper
                            .selectByKey("PMSExportDYPath");
                    if (exportDataPathList != null && exportDataPathList.size() > 0) {
                        dir = exportDataPathList.get(0).getConfigValue();
                        dir = dir.replace("@COMPANYID", String.valueOf(companyId));
                        dir = dir.replace("@TIMESTAMP", String.valueOf(System
                                .currentTimeMillis()));
                    }
                    File fdir = new File(dir);
                    if (!fdir.exists()) {
                        fdir.mkdirs();
                    }
//					List<Config> exportExcelNameList = configMapper
//							.selectByKey("PMSExportDYName");
//					if (exportExcelNameList != null
//							&& exportExcelNameList.size() > 0) {
//						fileName = exportExcelNameList.get(0).getConfigValue();
//					}
                    _logger.info("dir=" + dir);
                    _logger.info("fileName=" + fileName);
                    FileOutputStream os = null;
                    try {
                        //os = new FileOutputStream(dir + "\\test1.xls");
                        os = new FileOutputStream(dir + fileName);
                        wb.write(os);
                        os.close();
                        returnCode = dir + fileName;
                    } catch (FileNotFoundException e) {
                        e.printStackTrace();
                    } catch (IOException e) {
                        e.printStackTrace();
                    } finally {
                        try {
                            os.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                } else {
                    _logger
                            .info("exportTemplateDefMapper.selectTemplateDefByTemplateId("
                                    + templateId
                                    + "):exportTemplateDefList.size() =0");
                }
            } else {
                _logger.info("exportTemplateMapper.selectTemplateByIdAndStatus("
                        + templateId + ",1):is null");
            }
            _logger.info("returnCode =======" + returnCode);
        } catch (Exception e) {
            _logger.info(e);
            e.printStackTrace();
        }
        return returnCode;
    }

    @Override
    public String ZYPMSExel(long companyId, long templateId, String LineCode, String deviceName) throws PwtzDatabaseException {
        // 返回成功失败表示:2为失败
        String returnCode = "2";
        try {
            ExportTemplate exportTemplate = exportTemplateMapper
                    .selectTemplateByIdAndStatus(templateId, 0);
            if (exportTemplate != null) {
                _logger.info("exportTemplate.getExcelPath()="
                        + exportTemplate.getExcelPath());
                String excelPath = exportTemplate.getExcelPath();
                File excelFile = new File(excelPath);
                if (!excelFile.exists()) {
                    return returnCode;
                }
                HSSFWorkbook wb = new HSSFWorkbook();
                try {
                    wb = new HSSFWorkbook(new FileInputStream(excelFile));
                } catch (FileNotFoundException e) {
                    return returnCode;
                } catch (IOException e) {
                    return returnCode;
                }
                List<ExportTemplateDef> exportTemplateDefList = exportTemplateDefMapper
                        .selectTemplateDefByTemplateId(templateId);
                _logger.info("exportTemplateDefList size ="
                        + exportTemplateDefList.size());
                if (exportTemplateDefList.size() > 0) {
                    for (int i = 0; i < exportTemplateDefList.size(); i++) {
                        ExportTemplateDef exportTemplateDef = new ExportTemplateDef();
                        exportTemplateDef = exportTemplateDefList.get(i);
                        // 通过EXECUTE_SQL生成对应每个sheet提取的表数据
                        String sheetSql = exportTemplateDef.getExecuteSql();
                        List<Map<String, Object>> mapList = simpleJdbcTemplate
                                .queryForList(sheetSql, LineCode);
                        _logger.info("mapList size =" + mapList.size() + "LineCode:" + LineCode);
                        // 获取对应sheet
                        _logger.info("exportTemplateDef.getSheetName() ="
                                + exportTemplateDef.getSheetName());
                        HSSFSheet sheet = wb.getSheet(exportTemplateDef
                                .getSheetName());
                        // 获取赋值行数
                        int rowIndex = exportTemplateDef.getStartRowIndex();
                        // 通过mapList进行excle赋值
                        if (mapList.size() > 0) {
                            for (int j = 0; j < mapList.size(); j++) {
                                HSSFRow row = sheet.createRow(rowIndex);
                                Map map = mapList.get(j);
                                HSSFCell cell = row.createCell(0);
                                sheet.autoSizeColumn(0, true);
                                cell.setCellValue(j + 1);
                                int k = 1;
                                for (Iterator it = map.keySet().iterator(); it
                                        .hasNext(); ) {
                                    String key = (String) it.next();
                                    cell = row.createCell(k);
                                    sheet.autoSizeColumn(k, true);
                                    cell.setCellValue((map.get(key) == null || map.get(key).equals("null") || map.get(key).equals("NULL")) ? "" : map.get(key) + "");
                                    k++;
                                }
                                rowIndex++;
                            }
                        } else {
                            _logger.info("jdbcTemplate.queryForList(" + sheetSql
                                    + "):mapList.size() =0");
                            if ((i + 1) == mapList.size()) {
                                return returnCode;
                            }
                        }
                    }
                    // 读取输出文件路径并输出excle
                    String dir = "";
                    String fileName = deviceName + ".xls";
                    List<Config> exportDataPathList = configMapper
                            .selectByKey("PMSExportDYPath");
                    if (exportDataPathList != null && exportDataPathList.size() > 0) {
                        dir = exportDataPathList.get(0).getConfigValue();
                        dir = dir.replace("@COMPANYID", String.valueOf(companyId));
                        dir = dir.replace("@TIMESTAMP", String.valueOf(System
                                .currentTimeMillis()));
                    }
                    File fdir = new File(dir);
                    if (!fdir.exists()) {
                        fdir.mkdirs();
                    }
                    _logger.info("dir=" + dir);
                    _logger.info("fileName=" + fileName);
                    FileOutputStream os = null;
                    try {
                        //os = new FileOutputStream(dir + "\\test1.xls");
                        os = new FileOutputStream(dir + fileName);
                        wb.write(os);
                        os.close();
                        returnCode = dir + fileName;
                    } catch (FileNotFoundException e) {
                        e.printStackTrace();
                    } catch (IOException e) {
                        e.printStackTrace();
                    } finally {
                        try {
                            os.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                } else {
                    _logger
                            .info("exportTemplateDefMapper.selectTemplateDefByTemplateId("
                                    + templateId
                                    + "):exportTemplateDefList.size() =0");
                }
            } else {
                _logger.info("exportTemplateMapper.selectTemplateByIdAndStatus("
                        + templateId + ",1):is null");
            }
            _logger.info("returnCode =======" + returnCode);
        } catch (Exception e) {
            _logger.info(e);
            e.printStackTrace();
        }
        return returnCode;
    }
}